Santosh Khatri Santosh Khatri - 1 month ago 6
MySQL Question

Join and count rows value in mysql?

I have two table in my database(first name - game_table and second name - attendee_table):

/ ---game_table-- \ / -- Attendee_table -- \
| g_id | g_name | | a_id | g_id | a_name | a_age |
| 101 | football | | 1 | 101 | Santosh | 31 |
| 102 | Cricket | | 2 | 101 | Parveen | 35 |
| 102 | Hockey | | 3 | 101 | Ram | 15 |
| 4 | 101 | Radhe | 10 |
| 5 | 101 | Salim | 31 |
| 6 | 101 | sandeep | 25 |
| 7 | 101 | Rahul | 40 |


I want following result:

| age_break_up | Count |
| 0 - 18 years | 2 |
| 19 - 25 years | 1 |
| 26 - 40 years | 4 |
| 41+ years | 0 |


so for that i am using following query :

mysqli_query($con, "SELECT f.a_age, COUNT(f.a_age) FROM game_table t INNER JOIN attendee_table f ON t.g_id = f.g_id GROUP BY f.a_age")


But this query return the following output:

| a_age | Count |
| 10 | 1 |
| 15 | 1 |
| 25 | 1 |
| 31 | 2 |
| 35 | 1 |
| 40 | 1 |


so how can i take my result like below:

| age_break_up | Count |
| 0 - 18 years | 2 |
| 19 - 25 years | 1 |
| 26 - 40 years | 4 |
| 41+ years | 0 |

Answer

If there is no pattern on splitting the groups (E.G. groups of 18 years which is not the case) then I think you have to use conditional grouping, which can be done using CASE EXPRESSION :

SELECT CASE WHEN f.a_age < 19 THEN '0 - 18 years'
            WHEN f.a_age between 19 and 25 THEN '19 - 25 years' 
            WHEN f.a_age between 26 and 40 THEN '26 - 40 years'
            ELSE '41+ years'
       end as age_break_up, 
       COUNT(f.a_age) 
FROM game_table t 
INNER JOIN attendee_table f
 ON t.g_id = f.g_id 
GROUP BY CASE WHEN f.a_age < 19 THEN '0 - 18 years'
              WHEN f.a_age between 19 and 25 THEN '19 - 25 years' 
              WHEN f.a_age between 26 and 40 THEN '26 - 40 years'
              ELSE '41+ years'
         END
Comments