Seemingly Complicated Query help needed

krashed · March 29th, 2006, 11:45 PM

Hi, its my first post. Usually i wouldve worked out my problems on my own... but this time due to certain time constraints, i'm really on wits end....

I have three tables with their fields as follows(insignificant fields are left out here):

ZONE:
- id

PROP:
- id
- type[string] < (either "typeA" or "typeB")

TAG:
-id
-propId
-zoneId

The scenario is as follows: Each prop would be attached with a tag and can be found in any one zone at a time.

I need ONE SQL STATEMENT to generate a table as like the following sample:

ZoneID TypeACount TypeBCount TotalCount
------ ---------- ---------- ----------
101 9 5 14
102 3 7 10

It would have been simple if not for the required schema design.... help anyone?

jrogers · March 30th, 2006, 04:42 AM

Try the following

Code:

Select
 Z.id ZoneID,
 count(case when p.type = 'Type A' then 1 else null end) TypeACount,
 count(case when p.type = 'Type B' then 1 else null end) TypeBCount,
 count(1) TotalCount
from
 Zone Z inner join Tag t on z.id = t.zoneid
 Inner join Prop p on z.propid = p.id
Group by
 z.id

krashed · March 30th, 2006, 05:57 AM

YOU ARE THE MAN!!
It worked good.
Thanks!

but here's another thing... if a zone has zer0 of typeA and typeB, the record for that zone wont be shown, is there something i can add for it to display '0' for typeACount and typeBCount instead of NOT showing up at all?

i'm working on it as well.

really.... thanks again

krashed · March 30th, 2006, 06:02 AM

ok, i changed the joins to left join instead of inner join. The zeroes would show up. But the TotalCount showed up a '1' instead of a 0

jrogers · March 30th, 2006, 06:24 AM

you could change the total count logic to

Code:

count(case when p.type = 'Type A' or p.type = 'Type B' then 1 else null end) TotalCount

not really sure this will work.. if you could send me some sample data I could take a look at it for you

krashed · March 31st, 2006, 01:02 AM

once again thanks a million
you're great.
it worked totally now.

:') *sniff