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 wrote:
> 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.
>
>

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.
jstucklex@attglobal.net
==================

"Jerry Stuckle" <jstucklex@attglobal.net> wrote in message
news:uLidnbqxJNTsf7nYnZ2dnUVZ_smdnZ2d@comcast.com. ..
> Bosconian wrote:
>> 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.
>>
>>
>
> 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.
> jstucklex@attglobal.net
> ==================

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

Bosconian <nobody@nowhere.com> schrieb:
> I'm looking the latter, a count of distinct items.

The Syntax is COUNT(DISTINCT foo)

Cheers
--
Philipp Tölke
PGP: 0x96A1FE7A

"Philipp Tölke" <ascii158@web.de> wrote in message
news:eg38ql$pqt$1@online.de...
> Bosconian <nobody@nowhere.com> schrieb:
>> I'm looking the latter, a count of distinct items.
>
> The Syntax is COUNT(DISTINCT foo)
>
> Cheers
> --
> Philipp Tölke
> PGP: 0x96A1FE7A

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

Many thanks.

Bosconian wrote:
> "Philipp Tölke" <ascii158@web.de> wrote in message
> news:eg38ql$pqt$1@online.de...
>
>>Bosconian <nobody@nowhere.com> schrieb:
>>
>>>I'm looking the latter, a count of distinct items.
>>
>>The Syntax is COUNT(DISTINCT foo)
>>
>>Cheers
>>--
>>Philipp Tölke
>>PGP: 0x96A1FE7A
>
>
> It's amazing what a difference moving one parenthesis makes. :-)
>
> Many thanks.
>
>

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.
jstucklex@attglobal.net
==================

"Jerry Stuckle" <jstucklex@attglobal.net> wrote in message
news:Y_mdnbN4ks_K0LjYnZ2dnUVZ_rmdnZ2d@comcast.com. ..
> Bosconian wrote:
>> "Philipp Tölke" <ascii158@web.de> wrote in message
>> news:eg38ql$pqt$1@online.de...
>>
>>>Bosconian <nobody@nowhere.com> schrieb:
>>>
>>>>I'm looking the latter, a count of distinct items.
>>>
>>>The Syntax is COUNT(DISTINCT foo)
>>>
>>>Cheers
>>>--
>>>Philipp Tölke
>>>PGP: 0x96A1FE7A
>>
>>
>> It's amazing what a difference moving one parenthesis makes. :-)
>>
>> Many thanks.
>
> 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.
> jstucklex@attglobal.net
> ==================

Absolutely. Thanks for the clarification.