Biggest age group in USA

I’m doing the “Biggest age group in USA” exercise, however, I was trying to experiment and to a little change in the analysis by trying to visualize the groups by country, but I keep getting an error with the following query:

SELECT
  CASE
    WHEN age < 13 THEN 'kid'
    WHEN age < 18 THEN 'teenager'
    WHEN age < 25 THEN 'college'
    WHEN age < 35 THEN 'young adult'
    WHEN age < 56 THEN 'middle age'
    ELSE 'older adult'
  END AS age_group,
  country,
  COUNT(*)
FROM users
GROUP BY 1
GROUP BY country

Thank you all!!

REPLIES

makaroni4

over 3 years Reply

Hi @ecasanova95

Great question here, the trick is that any query has only one GROUP BY statement. If we want to group by multiple columns – we need to list them separated by comma:

SELECT
  CASE
    WHEN age < 13 THEN 'kid'
    WHEN age < 18 THEN 'teenager'
    WHEN age < 25 THEN 'college'
    WHEN age < 35 THEN 'young adult'
    WHEN age < 56 THEN 'middle age'
    ELSE 'older adult'
  END AS age_group,
  country,
  COUNT(*)
FROM users
GROUP BY 1, 2

SELECT
  CASE
    WHEN age < 13 THEN 'kid'
    WHEN age < 18 THEN 'teenager'
    WHEN age < 25 THEN 'college'
    WHEN age < 35 THEN 'young adult'
    WHEN age < 56 THEN 'middle age'
    ELSE 'older adult'
  END AS age_group,
  country,
  COUNT(*)
FROM users
GROUP BY age_group, country

The first way is more handy: referring columns by their index (1 or 2) saves you some typing when you change the aggregation column name.

Biggest age group in USA

REPLIES

WRITE A REPLY