Reputation: 21
hope you could help me out!
I have the following database:
country | Date_of_birth
-----------------------
france | 1980-07-01
spain | 1978-03-23
greece | 1981-05-05
germany | 1974-08-11
I'm trying to get this result:
country | 13 - 17 | 25 - 45 | 65 - 75
-------------------------------------
france | | 1 |
spain | | 1 |
greece | | 1 |
Query I figured out this far:
SELECT SUM( IF( age BETWEEN 13 AND 17 , 1, 0)) AS '13 - 17',
SUM( IF( age BETWEEN 25 AND 45 , 1, 0)) AS '25 - 45',
SUM( IF( age BETWEEN 65 AND 75 , 1, 0)) AS '65 - 75'
FROM (SELECT TIMESTAMPDIFF( YEAR, Date_of_birth, CURDATE()) AS age, country FROM data
) AS test
GROUP BY country
HAVING country IN ("france","greece,"spain")
Result:
13 - 17 | 25 - 45 | 65 - 75
----------------------------
| 1 |
| 1 |
| 1 |
Any idea how i get the "country" column at the beginning?
Thanks in advance!!
Upvotes: 2
Views: 610
Reputation: 1437
If you want the "country" column included, you have to add it in the SELECT part:
SELECT
country,
SUM( IF( age BETWEEN 13 AND 17 , 1, 0)) AS '13 - 17',
SUM( IF( age BETWEEN 25 AND 45 , 1, 0)) AS '25 - 45',
SUM( IF( age BETWEEN 65 AND 75 , 1, 0)) AS '65 - 75'
FROM (SELECT TIMESTAMPDIFF( YEAR, Date_of_birth, CURDATE()) AS age, country FROM data
) AS test
GROUP BY country
HAVING country IN ("france","greece,"spain")
Upvotes: 1
Reputation: 425448
Having
only works on aggregated columns. You want a simple where
:
SELECT
country,
SUM(age BETWEEN 13 AND 17) AS '13 - 17',
SUM(age BETWEEN 25 AND 45) AS '25 - 45',
SUM(age BETWEEN 65 AND 75) AS '65 - 75'
FROM (SELECT TIMESTAMPDIFF( YEAR, Date_of_birth, CURDATE()) AS age, country FROM data
) AS test
WHERE country IN ('france', 'greece', 'spain')
GROUP BY country
Note also the simplification of the query: in mysql true
is 1
and false
is 0
, so you can simply sum the conditions - you don't need a case
.
Also, you had a syntax error - you needed single quotes around your country values.
Upvotes: 2