CODEWITHSUNDEEP
mysqlsql
Fadhel J Muhammad
Fadhel J Muhammad

Reputation: 109

Count Results Based on Categorize Range Age (Query MySQL)

I have a table like this:

| Results  | Age |
|----------------|
|    HT    | 20  |
|    HT    | 22  |
|    HT    | 55  |

How I can show the latest data like this:

|  RangeAge  | CountResult |
|--------------------------|
|    15-19   |      0      |
|    20-44   |      2      |
|    45-54   |      0      |
|    55-60   |      1      |
|    60-..   |      0      |

Upvotes: 1

Views: 71

Answers (2)

Ed Bangga
Ed Bangga

Reputation: 13006

Here's your query;

select t1.* from (
    select '15-19' as rAge, count(1) as ct from tableA where Age between 15 and 19
    union all
    select '20-24', count(1) from tableA where Age between 20 and 24
    union all
    select '45-54', count(1) from tableA where Age between 45 and 54
    union all
    select '55-60', count(1) from tableA where Age between 55 and 59
    union all
    select '60', count(1) from tableA where Age > 59) t1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269483

You need a derived table, in order to get the rows with a zero count:

select r.range, count(t.age)
from (select '15-19' as range, 15 as lo, 19 as hi  union all
      select '20-44' as range, 20 as lo, 44 as hi  union all
      . . . 
     ) r left join
     t
     on t.age >= r.lo and t.age <= r.hi
group by r.range
order by min(r.lo);

Upvotes: 3

Related Questions