I have a SQL query that counts mass murders caused by mental illness by year.
SELECT year, Count(year) FROM Table2
WHERE ((Table2.cause_ID=15) OR
(Table2.cause_ID=16))
GROUP BY Table2.year;
But there are years with no mental illness mass murders, and this query does not show rows for those years. How do I tell the query to insert rows with the intervening years and a 0 count?
It appears that I should create a table of years and counts initialized with years starting at my first row in Table2 up to the last row in Table2, then setting each count with the results of the above query. But Access does not like:
create table allyears (year int, count int );
It shows a syntax error on the next statement after the semicolon.
The solution:
SELECT year, Abs(Sum([cause_ID] In (15, 16))) AS incidents FROM Table2 GROUP BY year;
Try adding OR (Table2.cause_ID=Null) to return rows with null values (no data)?
ReplyDeleteSame output, but it seems like a good approach. Maybe OR (Table2.cause_ID != 15) AND (Table2.cause_ID != 15)
DeleteMaybe create a variable which equals mental illness mass murders plus 1 for each year?
ReplyDeleteget the results of those numbers, a small number of years, and subtract 1 from each.
I presume Table2 has all the years you want, as it has mass murders from other causes as well?
ReplyDeleteThis might not work in Access, it's been forever since I used it.
SELECT
years.year,
COALESCE(incidents.cnt, 0) AS cnt
FROM
(SELECT DISTINCT year FROM Table2) AS years
LEFT OUTER JOIN (
SELECT year, COUNT(*) AS cnt
FROM Table2
WHERE cause_ID IN (15, 16)
GROUP BY year
) AS incidents ON
incidents.year = years.year
;
Maybe create a UNION with SELECT year, Count(year) FROM Table2 WHERE ((Table2.cause_ID<>15) AND
ReplyDelete(Table2.cause_ID<>16))
1. Create a table 'yearlist', populated with entries for each of the covered years, on a single row, 'year'
ReplyDelete2. use a 'join' to permit showing null (zero count) rows as well as populated ones
SELECT year, Count(year) FROM Table2
left join yearlist.year
on Table2.year = yearlist.year
WHERE ((Table2.cause_ID=15) OR
(Table2.cause_ID=16))
order by Table2.year
GROUP BY Table2.year
;
I forget if the 'group by' follows, or precedes an 'order'
You want to use sum with case.
ReplyDeleteSELECT
Year,
SUM(
CASE
WHEN cause_ID=15 THEN 1
WHEN cause_ID=16 THEN 1
ELSE O
END
) AS NumberOfIncidents
FROM
Table2
GROUP BY
Year
ORDER BY
Year ASC;
Wayne