Professional Web Applications Themes

Count(*) different than record count - MySQL

Can someone explain why the following query will return 80 records: SELECT DISTINCT c.caseid FROM cases c LEFT JOIN casespecialty cs ON c.caseid = cs.caseid LEFT JOIN examspecialty es ON c.caseid = es.caseid WHERE (cs.uid = 86 OR es.uid = 86) but when c.caseid is wrapped with Count() the query will return 225: SELECT DISTINCT Count(c.caseid) FROM cases c LEFT JOIN casespecialty cs ON c.caseid = cs.caseid LEFT JOIN examspecialty es ON c.caseid = es.caseid WHERE (cs.uid = 86 OR es.uid = 86) The correct answer in this case is 80. I'm looking for Count() to return the correct (desired) ...

  1. #1

    Default Count(*) different than record count

    Can someone explain why the following query will return 80 records:

    SELECT DISTINCT c.caseid FROM cases c LEFT JOIN casespecialty cs ON c.caseid
    = cs.caseid LEFT JOIN examspecialty es ON c.caseid = es.caseid WHERE (cs.uid
    = 86 OR es.uid = 86)

    but when c.caseid is wrapped with Count() the query will return 225:

    SELECT DISTINCT Count(c.caseid) FROM cases c LEFT JOIN casespecialty cs ON
    c.caseid = cs.caseid LEFT JOIN examspecialty es ON c.caseid = es.caseid
    WHERE (cs.uid = 86 OR es.uid = 86)

    The correct answer in this case is 80. I'm looking for Count() to return the
    correct (desired) total.


    Bosconian Guest

  2. #2

    Default Re: Count(*) different than record count

    Bosconian wrote: 

    Do you want a distinct count, or a count of distinct items?

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  3. #3

    Default Re: Count(*) different than record count

    "Jerry Stuckle" <net> wrote in message
    news:com... 
    >
    > Do you want a distinct count, or a count of distinct items?
    >
    > --
    > ==================
    > Remove the "x" from my email address
    > Jerry Stuckle
    > JDS Computer Training Corp.
    > net
    > ==================[/ref]

    I'm looking the latter, a count of distinct items.


    Bosconian Guest

  4. #4

    Default Re: Count(*) different than record count

    Bosconian <com> schrieb: 

    The Syntax is COUNT(DISTINCT foo)

    Cheers
    --
    Philipp Tölke
    PGP: 0x96A1FE7A
    Philipp Guest

  5. #5

    Default Re: Count(*) different than record count

    "Philipp Tölke" <de> wrote in message
    news:eg38ql$pqt$de... 
    >
    > The Syntax is COUNT(DISTINCT foo)
    >
    > Cheers
    > --
    > Philipp Tölke
    > PGP: 0x96A1FE7A[/ref]

    It's amazing what a difference moving one parenthesis makes. :-)

    Many thanks.


    Bosconian Guest

  6. #6

    Default Re: Count(*) different than record count

    Bosconian wrote: 
    >>
    >>The Syntax is COUNT(DISTINCT foo)
    >>
    >>Cheers
    >>--
    >>Philipp Tölke
    >>PGP: 0x96A1FE7A[/ref]
    >
    >
    > It's amazing what a difference moving one parenthesis makes. :-)
    >
    > Many thanks.
    >
    >[/ref]

    Yes, you were getting a distinct count - hence my question (trying to
    steer you onto the right course).

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  7. #7

    Default Re: Count(*) different than record count

    "Jerry Stuckle" <net> wrote in message
    news:com... 
    >>
    >>
    >> It's amazing what a difference moving one parenthesis makes. :-)
    >>
    >> Many thanks.[/ref]
    >
    > Yes, you were getting a distinct count - hence my question (trying to
    > steer you onto the right course).
    >
    > --
    > ==================
    > Remove the "x" from my email address
    > Jerry Stuckle
    > JDS Computer Training Corp.
    > net
    > ==================[/ref]

    Absolutely. Thanks for the clarification.


    Bosconian Guest

Similar Threads

  1. Count Record Problem
    By patelajk in forum Dreamweaver AppDev
    Replies: 0
    Last Post: April 17th, 03:28 PM
  2. total record count in datagrid
    By DC Gringo in forum ASP.NET Data Grid Control
    Replies: 1
    Last Post: March 4th, 08:32 PM
  3. Stop adding record in subform after record count = 1
    By Charlie in forum Microsoft Access
    Replies: 0
    Last Post: July 31st, 10:42 AM
  4. How to count record selects?
    By TimC in forum Oracle Server
    Replies: 3
    Last Post: December 19th, 09:37 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139