3

Please don't make it flag (it's not duplicate) i search it on google can't find any answer.

I have below result in mysql

|18-30 | 30 - 45 | 45 - 60 | 60+  
|  28  |  26     |   12    | 5

I want it like following result-

|18-30 |  28 |   
|30 -45|  26 |   
|45-60 |  12 |  
| 60+  |  5  |

How i can get it in mysql?

Sunil Kothiyal
  • 157
  • 1
  • 12
  • How do you get your original result? – Gordon Linoff Mar 10 '18 at 13:47
  • Possible duplicate of [MySQL - Rows to Columns](https://stackoverflow.com/questions/1241178/mysql-rows-to-columns) – Eray Balkanli Mar 10 '18 at 13:48
  • 1
    SELECT SUM(IF((SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(Birth_Date, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(Birth_Date, '00-%m-%d')) AS age) BETWEEN 18 and 30,1,0)) as '18-30', SUM(IF((SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(Birth_Date, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(Birth_Date, '00-%m-%d')) AS age) BETWEEN 30 and 45,1,0)) as '30 - 45', ................................... FROM tablename – Sunil Kothiyal Mar 10 '18 at 13:49

1 Answers1

6

The simplest way is union all:

select '18-30' as range, `18-30` as val from t union all
select '30 - 45', `18-30` from t union all
select '45 - 60', `45 - 60` from t union all
select '60+',  `60+`;

However, your results are probably coming from a conditional aggregation. I would expect that you can do something like this instead:

select (case when age < 30 then '18-29'
             when age < 45 then '30-44'
             when age < 60 then '45-59'
             else '60+'
        end) as agegrp,
       count(*)
from . . .
where age >= 18
group by agegrp;
order by min(age)
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    actually this is just a date of birth in table i am converting it to age range by above query and i am getting result as i represant in top result i need it as i told. – Sunil Kothiyal Mar 10 '18 at 13:52
  • @Sunny . . . "date of birth" is not a number between 18 and 60. Are you suggesting that the first query does not work? If the second part of the answer doesn't describe your query (it was just a guess after all), then you can ask another question with more information about the query you are really using. – Gordon Linoff Mar 10 '18 at 17:18
  • @ Gordon Linoff I updated question please review once again. – Sunil Kothiyal Mar 10 '18 at 17:26
  • @Sunny . . . You should ask *another* question, not edit this question. Your original question was quite clear and this answers it. Changing the question to invalidate this answer is not nice. – Gordon Linoff Mar 10 '18 at 17:27