SELECT SUM( "MassMurder"."ID" ) AS "ID" FROM "MassMurder" "MassMurder" GROUP BY "month", "date", "year", "city", "state", "dead victim count", "undead victim count", "suicide?", "UNKNOWN", "AX", "HATCHET", "KNIFE", "OTHER SHARP", "BLUNT", "EXPLOSIVE", "POISON", "STRANGLE", "DROWN", "ARSON", "HANG", "OTHER", "FIREARM (unidentified)", "shotgun", "rifle", "pistol", "machine gun", "cause", "category" where "MassMurder"."AX" = 'TRUE' OR "MassMurder"."HATCHET" = 'TRUE'
The WHERE clause seems defective in some way. Am I not testing a boolean field correctly? I cannot find any examples by searching for a WHERE clause using a boolean field. Everything else, but not that.
A couple of points:
ReplyDeleteYour WHERE clause is in the wrong place. General query syntax is:
SELECT
[column list]
FROM
[table list]
WHERE
[conditions]
GROUP BY
[grouping]
HAVING
[aggregate conditions]
ORDER BY
[specified sort order]
Your WHERE clause is after the GROUP BY clause.
Not all SQL engines have a BOOLEAN datatype (most don't).
Your WHERE clause as written is looking for rows where "AX" is literally equal to the text string 'TRUE' or "HATCHET" is literally equal to the text string 'TRUE'.
What are the values in those columns?
Try:
SELECT DISTINCT
mm."AX"
FROM
"MassMurder" mm;
and
SELECT DISTINCT
mm."HATCHET"
FROM
"MassMurder" mm;
Best of luck!
Try true without quotes. Also, if it's not actually a boolean, but an int, you might need to use 1/-1/whatever).
ReplyDeleteI am not positive about Access but generally you shouldn't need quotes around fields anyway, unless there's a space in the field name.
Not sure why you'd want to SUM the id's. Maybe you mean COUNT?
ReplyDeleteYou should use back-ticks (i.e. `) instead of double quotes to quote table and field names.
You have: FROM "MassMurder" "MassMurder"
So you're specifying two tables, of the same name, without a comma separating them.
Do you really have a column named "suicide?" with a question mark in it?
In GROUP BY clauses, you don't use WHERE, you use HAVING.
That's just off the top of my head, but I'm not sure what you're trying to query for.
If you can share the schema for the table, and what you need the query to report, I can give you a hand.
Remove the quotes around the word TRUE. Your WHERE clause is searching for the literal text "TRUE", no the Boolean value True.
ReplyDelete