In Access:
SELECT Table2.category_ID AS category, count(*) AS incidents, sum(Table2.dead) AS dead, avg(Table2.dead) AS [avg]
FROM Table2
GROUP BY category_ID;
returns a table with the category table correctly looked up:
Saturday, January 14, 2023
SQL Question
Subscribe to:
Post Comments (Atom)
MS Access tries to be 'helpful' and auto-magically includes the name value from the related category record.
ReplyDeleteAssuming the category table is called 'category' and has columns 'category_id' and 'category_name', you could use the following query:
SELECT
c.category_name,
COUNT(T2.*) AS number_of_incidents,
SUM(T2.dead) AS number_of_dead,
AVG(T2.dead) AS average_number_of_dead
FROM
Table2 T2
JOIN category c
ON T2.category_id=c.category_id
GROUP BY
c.category_name;