Job specificaitons: SQL Database first time Job study guide

SQL Query: Group students by Age, Male, Female, and Total

I would like to create some statistics about my users.

I want to group them in based on intervals of their ages, and sum them up based on their genders. So in the end i get a table like this:

   age | male | female | total
  0-18 |    2 |      1 |     3
 18-25 |    3 |      4 |     7
25-100 |   13 |     25 |    38
 total |   18 |     30 |    48

(btw. these are example intervals)

Rightnow i do this by running this query for every row:

SELECT
    SUM(IF(gender = 'male', 1,0)) AS `male`,
    SUM(IF(gender = 'female', 1,0)) AS `female`,
    COUNT(gender) AS `total`
FROM `users`
WHERE 
    `birthday` 
        BETWEEN 
            DATE_SUB(CURDATE(), INTERVAL 25 YEAR) 
        AND 
            DATE_SUB(CURDATE(), INTERVAL 18 YEAR)

and sum the results up by my php script to create the last total row

how could I combine all these querys to improve the performance and maybe also create the last total line via mysql.

The intervals i want to use are:

0-18
18-25
25-31
31-36
36-41
41-46
46-51
51-56
56-61
61-100

Solution:

SELECT  Age,
        SUM(gender = 'female') Female,
        SUM(gender = 'male') Male,
        COUNT(*) TotalPerson
FROM
        (
            SELECT  CASE 
                        WHEN FLOOR(DATEDIFF(CURDATE(), birthday) / 365) BETWEEN 0 AND 18 THEN '00-18'
                        WHEN FLOOR(DATEDIFF(CURDATE(), birthday) / 365) BETWEEN 19 AND 25 THEN '19-25'
                        WHEN FLOOR(DATEDIFF(CURDATE(), birthday) / 365) BETWEEN 26 AND 31 THEN '26-31'
                        WHEN FLOOR(DATEDIFF(CURDATE(), birthday) / 365) BETWEEN 32 AND 36 THEN '32-36'
                        WHEN FLOOR(DATEDIFF(CURDATE(), birthday) / 365) BETWEEN 37 AND 41 THEN '37-41'
                        WHEN FLOOR(DATEDIFF(CURDATE(), birthday) / 365) BETWEEN 42 AND 46 THEN '42-46'
                        WHEN FLOOR(DATEDIFF(CURDATE(), birthday) / 365) BETWEEN 47 AND 51 THEN '47-51'
                        WHEN FLOOR(DATEDIFF(CURDATE(), birthday) / 365) BETWEEN 52 AND 56 THEN '52-56'
                        WHEN FLOOR(DATEDIFF(CURDATE(), birthday) / 365) BETWEEN 57 AND 61 THEN '57-61'
                        WHEN FLOOR(DATEDIFF(CURDATE(), birthday) / 365) BETWEEN 62 AND 100 THEN '62-100'
                    END Age,
                   Gender
            FROM   users
        ) ageList
GROUP   BY Age

• SQLFiddle Demo
• SQLFiddle Demo (WITH ROLLUP)

OUTPUT

╔════════╦════════╦══════╦═════════════╗
║  AGE   ║ FEMALE ║ MALE ║ TOTALPERSON ║
╠════════╬════════╬══════╬═════════════╣
║ 00-18  ║      0 ║    1 ║           1 ║
║ 19-25  ║      6 ║    5 ║          11 ║
║ 26-31  ║     22 ║    4 ║          26 ║
║ 32-36  ║     39 ║   16 ║          56 ║
║ 37-41  ║     20 ║   11 ║          31 ║
║ 42-46  ║     31 ║    6 ║          38 ║
║ 47-51  ║     18 ║    3 ║          21 ║
║ 52-56  ║     11 ║    6 ║          17 ║
║ 57-61  ║     10 ║    6 ║          16 ║
║ 62-100 ║     12 ║   12 ║          24 ║
╚════════╩════════╩══════╩═════════════╝

OUTPUT with using WITH ROLLUP

╔════════╦════════╦══════╦═════════════╗
║  AGE   ║ FEMALE ║ MALE ║ TOTALPERSON ║
╠════════╬════════╬══════╬═════════════╣
║ 00-18  ║      0 ║    1 ║           1 ║
║ 19-25  ║      6 ║    5 ║          11 ║
║ 26-31  ║     22 ║    4 ║          26 ║
║ 32-36  ║     39 ║   16 ║          56 ║
║ 37-41  ║     20 ║   11 ║          31 ║
║ 42-46  ║     31 ║    6 ║          38 ║
║ 47-51  ║     18 ║    3 ║          21 ║
║ 52-56  ║     11 ║    6 ║          17 ║
║ 57-61  ║     10 ║    6 ║          16 ║
║ 62-100 ║     12 ║   12 ║          24 ║
║ TOTAL  ║    169 ║   70 ║         241 ║
╚════════╩════════╩══════╩═════════════╝