vendredi 24 juin 2016

SQL aggregation with case statement and group by

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:

enter image description here

TKS!

P.S.- this is part of coursera sql learning course, so I'm working on Jupyter.

Aucun commentaire:

Enregistrer un commentaire