Pages

▼

Monday, January 25, 2021

SQL Question

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;

at

7 comments:

  1. Dean in AzJanuary 25, 2021 at 6:15 PM

    Try adding OR (Table2.cause_ID=Null) to return rows with null values (no data)?

    ReplyDelete
    Replies
    1. Clayton CramerJanuary 26, 2021 at 11:46 AM

      Same output, but it seems like a good approach. Maybe OR (Table2.cause_ID != 15) AND (Table2.cause_ID != 15)

      Delete
  2. Dean WeingartenJanuary 25, 2021 at 7:02 PM

    Maybe create a variable which equals mental illness mass murders plus 1 for each year?

    get the results of those numbers, a small number of years, and subtract 1 from each.

    ReplyDelete
  3. John HardinJanuary 25, 2021 at 7:06 PM

    I presume Table2 has all the years you want, as it has mass murders from other causes as well?

    This 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
    ;

    ReplyDelete
  4. T TownJanuary 25, 2021 at 7:56 PM

    Maybe create a UNION with SELECT year, Count(year) FROM Table2 WHERE ((Table2.cause_ID<>15) AND
    (Table2.cause_ID<>16))

    ReplyDelete
  5. R P HerroldJanuary 26, 2021 at 9:12 AM

    1. Create a table 'yearlist', populated with entries for each of the covered years, on a single row, 'year'

    2. 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'

    ReplyDelete
  6. Wayne JohnsonJanuary 26, 2021 at 12:12 PM

    You want to use sum with case.

    SELECT
    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

    ReplyDelete