Wednesday, October 10, 2018

I Hate Doing This The Hard Way

I was hoping to write a SQL query to produce a table showing mass murders and dead by decade: 1790-1799, 1800-1809, subtotals and then totals, ideally with counts for each weapon category, but I cannot get the GROUP BY clause to work, so I am doing this as a series of separate queries.
SELECT "MassMurder"."ID" AS "ID", "MassMurder"."month" AS "month", "MassMurder"."date" AS "date", "MassMurder"."year" AS "year", "MassMurder"."city" AS "city", "MassMurder"."state" AS "state", "MassMurder"."dead victim count" AS "dead victim count", "MassMurder"."undead victim count" AS "undead victim count", "MassMurder"."suicide?" AS "suicide?", "MassMurder"."UNKNOWN" AS "UNKNOWN", "MassMurder"."AX" AS "AX", "MassMurder"."HATCHET" AS "HATCHET", "MassMurder"."KNIFE" AS "KNIFE", "MassMurder"."OTHER SHARP" AS "OTHER SHARP", "MassMurder"."BLUNT" AS "BLUNT", "MassMurder"."EXPLOSIVE" AS "EXPLOSIVE", "MassMurder"."POISON" AS "POISON", "MassMurder"."STRANGLE" AS "STRANGLE", "MassMurder"."DROWN" AS "DROWN", "MassMurder"."ARSON" AS "ARSON", "MassMurder"."HANG" AS "HANG", "MassMurder"."OTHER" AS "OTHER", "MassMurder"."FIREARM (unidentified)" AS "FIREARM (unidentified)", "MassMurder"."shotgun" AS "shotgun", "MassMurder"."rifle" AS "rifle", "MassMurder"."pistol" AS "pistol", "MassMurder"."machine gun" AS "machine gun", "MassMurder"."cause" AS "cause", "MassMurder"."category" AS "category" FROM "MassMurder" "MassMurder" WHERE "MassMurder"."year" >= 1800 AND "MassMurder"."year" < 1810
Yes wrong way to do it.

1 comment:

C. Petro said...

I just learned today that you when you are using a group by you have to have the *rest* of your variables in aggregate functions.

So for example:

Select decade, count(murders), sum(deaths) from some_table group by decade;