Forum Discussion
Inserting empty rows
This should be simple, but my SQL skills are rusty. I have a query that extracts the year and a count of incidents from a table.
SELECT year, Count(year) AS incidents
FROM Table2
WHERE ((Table2.cause_ID=15) OR (Table2.cause_ID=16))
GROUP BY year;
Of course, many years have no matching cause_ID = 15 or 16. I would like an output of year and 0 for those years.
Clayton_cramer If you don't like IIF, then use IN:
SELECT year, Abs(Sum([cause_ID] In (15, 16))) AS incidents FROM Table2 GROUP BY year;
If you want to see all years regardless of whether there are any matching cause_IDs for those years, then you can't use the WHERE clause to exclude them.
Instead, you need to use a conditional for the Count(year) to return the actual Count where there are matching cause_IDs or 0 where there are none.
SELECT year, Iif([cause_ID] = 15 or [cause_ID]=16), count(year), 0) AS incidents
FROM Table2
GROUP BY year;
wrong number of arguments used in query function lif([cause_ID) = 15 or [cause_ID]=16'.
Clayton_cramerlif looks like a typo for if, but "undefined function 'if' in expression."
- Excellent.
