Tuesday, June 18, 2013

CFWheels: SQL Counting rows with specific values

In SQL, you can count up rows that have a certain criteria, which means in one query you can count up multiple kinds of things:

select
   SUM(CASE WHEN dateResolved IS NULL THEN 1 ELSE 0 END) as itemsOutstanding,
   SUM(CASE WHEN dateResolved IS NOT NULL THEN 1 ELSE 0 END) as itemsResolved,
   COUNT(*) as total
from tableName
group by Year(InspectionDate) AS inspectedYear, Month(inspectionDate) AS inspectedMonth