Please be gentle! - this is my first post and I'm a total SQL novice thrown in at the deep end at work.

I need to output something like:

Name1 count#1 count#2 count#3 count#4
Name2 count#1 count#2 count#3 count#4
etc...

count#1 needs to be all the entries with a given "Name"
count#2 needs to be the subset of those that also has field#2 = value#1
count#3 needs to be the subset of those that also has field#2 = value#2
etc...

I can get something like:

SELECT
Name,
otherData
FROM
DBName
WHERE
field#3= value AND field#4 NOT IN ('valueA', 'valueB', 'valueC')
ORDER BY
Name

And that returns the subset I want to count, but as soon as I try replacing the "otherData" in the SELECT with:

count (when field#2=value#2 then 1 else 0 end) as count#1,
count (when field#2=value#3 then 1 else 0 end) as count#2,
count (when field#2=value#4 then 1 else 0 end) as count#3,

and adding this at the end:

GROUP BY
Name

Then I get totally silly counts.

I'm going round in circles trying to fix this, and no amount of web surfing is giving me any real clues. HELP!!!

I would suggest that a UNION would work

Originally Posted by chrishirst

I would suggest that a UNION would work

Would that solve the silly counts I'm getting?

e.g. when I run a simpe query I see that 3 entries match the Name1 count#1 criteria, but what I get when I include the count (when...) is 41 and not 3.

in theory it should, because all you need to do would be to replicate your first working query in each union with different criteria.

Originally Posted by chrishirst

all you need to do would be to replicate your first working query

but I haven't got even one of the count() returning a correct value...

add;

SELECT COUNT(column) AS rows, rest_of_fields

to the query that returns the correct subset

The count needs to be conditional - as per my first post...

Chris gave you a working answer, and a link to documentation that will fill in all the blanks. Put your criteria in a where clause in the query Chris spelled out for you to make it conditional, and then use the UNION operator to bring in your other counts.