Tallying Counts in SQL

SQL

I have forgotten this at least half a dozen times and then have to re-ask Chip, so I am writing it down. When attempting to aggregate how many times something occurred, use a case statement on the item in question to output a true/false or 1/0 (tally) for whether the thing we’re looking for occurred. You can then aggregate this in your output query by summing the aggregation.

case
	when USER_BILLABLE_TYPE = 'Secondary' then datediff(day, gap_first_recognized, OCCURRED_AT)
	else null
	end as secondary_lag_time

case
	when days_between_pregnancy_creation_and_gap <= 1 then true
	else false
end as gap_immediately_fired

min(case when lag_time is not null then true else false end) as was_seen

sum(case when IS_SATISFIED = true then 1 else 0 end) as "Satisfied"