CODEWITHSUNDEEP
mysql
user1224364
user1224364

Reputation: 21

sql between age group by

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

Answers (3)

Avi
Avi

Reputation: 1346

SELECT country, SUM...., SUM..., SUM...

Upvotes: 0

MysticXG
MysticXG

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

Bohemian
Bohemian

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

Related Questions