Symfony, PHP, Life, Parenting, Management, etc :)
Posts tagged sql
Transposing Rows into Columns with MySQL
013 years ago
This is very basic, but figured there might be people out there that would find this useful. Say you have a table with signups to your site, and you want to chart these signups. However there are three different signups: guest, paid, pro. Let’s say, for argument sake you have a table with 2 columns, date and signup type, like below:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
insert into t1 (dater, type) values ('2012-09-01', 'guest'), ('2012-09-01', 'guest'), ('2012-09-01', 'guest'), ('2012-09-01', 'paid'), ('2012-09-01', 'pro'), ('2012-09-01', 'paid'), ('2012-09-01', 'guest'), ('2012-09-20', 'guest'), /* note the change in date here */ ('2012-09-20', 'pro'), ('2012-09-20', 'pro'), ('2012-09-20', 'pro'), ('2012-09-20', 'paid'); |
If you had to run the following group by query you’d get the results row by row:
|
1 |
select dater, type, count(*) from t1 group by dater, type; |
… resulting in the following output:
|
1 2 3 4 5 6 7 8 |
dater type count(*) ---------------------------- 2012-09-01 guest 4 2012-09-01 pro 1 2012-09-01 paid 1 2012-09-20 guest 1 2012-09-20 pro 3 2012-09-20 paid 1 |
Let’s say, however, you want to have each date in one row, yet you want to have a column for guest, another column for paid and yet another for pro with the resulting counts in them? Well that’s easy, here’s what you do in SQL:
|
1 2 3 4 5 |
select dater, sum(if(type="guest",1,0)) guest, sum(if(type="paid",1,0)) paid, sum(if(type="pro",1,0)) pro from t1 group by dater; |
… and then you have output like this:
|
1 2 3 4 |
dater guest paid pro ------------------------------- 2012-09-01 4 1 1 2012-09-20 1 1 3 |
What’s awesome about doing it this way, is that you could copy this into Excel or whatever the tool is you use and line chart these figures against each other for a nice visual representation of your signups (or whatever you use this for) 🙂
Let me know what you think!
