I'm trying to understand the problem with this code:
SELECT COUNT(CASE liveIn.state
WHEN ("NY" OR "NJ") THEN "group1"
WHEN ("NC" or "SC") THEN "group2"
END) AS state_groups
FROM (SELECT DISTINCT user_guid, state
FROM users
WHERE country="US" AND country IS NOT NULL) AS liveIn
GROUP BY state_groups;
The error I get is: "Can't group on 'state_groups'"
I have other code that solve my problem which look like this (but I'm trying to understand the problem with the one above):
SELECT COUNT(DISTINCT user_guid),
CASE
WHEN (state="NY" OR state="NJ") THEN "group1"
WHEN (state="NC" OR state="SC") THEN "group2"
END AS state_groups
FROM users
WHERE country="US" AND country IS NOT NULL
GROUP BY state_groups;
my output should look like this:
TKS!
P.S.- this is part of coursera sql learning course, so I'm working on Jupyter.
Aucun commentaire:
Enregistrer un commentaire