>your trick of "sum( iff( ) )" as a column works exactly how i want. geni us.
>
>i knew i needed to join my results query on the group-by year query. just didn't know how to write the counters... the sum( if() ) does it.
>
>i have multiple tables i want to perform this on and realize now that i can't combine them all; it will be one query per table that as the counts. but that makes perfect sense to me now too.

Actually, sometimes you can do several counts within one SQL, but then you need to change things slightly:

count(distinct iif(<somecondition>, <sometable>.<PrimaryKey>, NULL))

and you might need to use LEFT JOIN rather than (INNER) JOIN.

Having a couple of such counts within a query are sometimes useful, but if you mix too much unrelated stuff within the same query, you may end up with a complex query that works perfectly, but that is completely unreadable.

Set