You have multiple columns. You want to sum those columns, into sums for each column. Many of the columns are empty, so I know that I need NVL. All the examples that I can find involve summming all the columns.
Something like
SELECT DISTINCTROW Sum([incidents].[UNKNOWN]) AS [Sum Of UNKNOWN], Sum([incidents].[AX]) AS [Sum Of AX], Sum([incidents].[HATCHET]) AS [Sum Of HATCHET], Sum([incidents].[KNIFE]) AS [Sum Of KNIFE], Sum([incidents].[OTHERSHARP]) AS [Sum Of OTHERSHARP], Sum([incidents].[BLUNT]) AS [Sum Of BLUNT], Sum([incidents].[EXPLOSIVE]) AS [Sum Of EXPLOSIVE], Sum([incidents].[POISON]) AS [Sum Of POISON], Sum([incidents].[STRANGLE]) AS [Sum Of STRANGLE], Sum([incidents].[DROWN]) AS [Sum Of DROWN], Sum([incidents].[ARSON]) AS [Sum Of ARSON], Sum([incidents].[HANG]) AS [Sum Of HANG], Sum([incidents].[OTHER]) AS [Sum Of OTHER], Sum([incidents].[personal]) AS [Sum Of personal], Sum([incidents].[FIREARM_UNKNOWN]) AS [Sum Of FIREARM_UNKNOWN], Sum([incidents].[SHOTGUN]) AS [Sum Of SHOTGUN], Sum([incidents].[RIFLE]) AS [Sum Of RIFLE], Sum([incidents].[PISTOL]) AS [Sum Of PISTOL], Sum([incidents].[MACHINE_GUN]) AS [Sum Of MACHINE_GUN], Sum([incidents].[AIRCRAFT]) AS [Sum Of AIRCRAFT]
FROM incidents;
but sum(dead) WHERE hatchet=1 and so on for all the weapons.
Clayton:
ReplyDeleteYou are looking for the 'CASE' statement.
https://www.techonthenet.com/access/functions/advanced/case.php
For example:
SELECT
SUM(CASE WHEN [incidents].[UNKNOWN] = 1 THEN 1 ELSE 0 END) AS UnknownCount,
SUM(CASE WHEN [incidents].[UNKNOWN] = 1 THEN [incidents].[dead] ELSE 0 END) AS UnknownFatalities,
SUM(CASE WHEN [incidents].[AX] = 1 THEN 1 ELSE 0 END) AS AxCount,
SUM(CASE WHEN [incidents].[AX] = 1 THEN [incidents].[dead] ELSE 0 END) AS AxFatalities
FROM
[incidents];
Or, for numbers by decade:
SELECT
INT([incidents].[year]/10)*10 AS Decade,
SUM(CASE WHEN [incidents].[UNKNOWN] = 1 THEN 1 ELSE 0 END) AS UnknownCount,
SUM(CASE WHEN [incidents].[UNKNOWN] = 1 THEN [incidents].[dead] ELSE 0 END) AS UnknownFatalities,
SUM(CASE WHEN [incidents].[AX] = 1 THEN 1 ELSE 0 END) AS AxCount,
SUM(CASE WHEN [incidents].[AX] = 1 THEN [incidents].[dead] ELSE 0 END) AS AxFatalities
FROM
[incidents]
GROUP BY
INT([incidents].[year]/10)*10
ORDER BY
INT([incidents].[year]/10)*10 ASC;
Best of luck!
Wayne
I would have a tendency to not want to to so much with SQL. Instead, I'd write a query that has all the fields I want and then write a regular program (in VBA or whatever Access wants) to actually do the counting. Probably be a lot simpler than trying to write a gigantic SQL query. (But as I've said before I haven't done a lot of this in Access/Word/whatever so I'm not sure exactly how to do it there. But think of it kind of like iterating over a SQL query obtained via JDBC in Java.)
ReplyDelete