Select row that has similar name

Question

So I have a tbl_total like this.

Name   | Total  |  Month
=========================
David  | 87     | Jan
Mike   | 67     | Jan
David  | 90     | Feb
Mike   | 100    | Feb

And I want to display it like this. Could please someone tell me how to do this coz i've no idea at all how to display like this in PHP.

Name   | Jan | Feb
===================
David  | 87  | 90
Mike   | 67  | 100

Can you show what you already have? Also why don't you combine the duplication in (My)SQL? — S.Pols, Nov 05 '14 at 12:14
Googling for "mysql pivot" brings back to StackOverFlow http://stackoverflow.com/questions/7674786/mysql-pivot-table — Yang, Nov 05 '14 at 12:16
You've tagged this with PHP, so don't bother with a pivot. Instead, just return a well-ordered result set and handle any issues of data display in the presentation layer (e.g. a simple PHP loop). There are billions of examples out there. — Strawberry, Nov 06 '14 at 10:55

juergen d · Answer 1 · 2014-11-05T12:29:30.197

7

select name,
       sum(case when month = 'Jan' then total else 0 end) as Jan,
       sum(case when month = 'Feb' then total else 0 end) as Feb,
       sum(case when month = 'Mar' then total else 0 end) as Mar,
       sum(case when month = 'Apr' then total else 0 end) as Apr,
       sum(case when month = 'May' then total else 0 end) as May,
       sum(case when month = 'Jun' then total else 0 end) as Jun,
       sum(case when month = 'Jul' then total else 0 end) as Jul,
       sum(case when month = 'Aug' then total else 0 end) as Aug,
       sum(case when month = 'Sep' then total else 0 end) as Sep,
       sum(case when month = 'Oct' then total else 0 end) as Oct,
       sum(case when month = 'Nov' then total else 0 end) as Nov,
       sum(case when month = 'Dec' then total else 0 end) as `Dec`
from your_table
group by name

edited Nov 05 '14 at 12:29

answered Nov 05 '14 at 12:15

juergen d

201,996
37
293
362

You can't, but you can accept this answer.. which is the same as 2 upvotes ;) – Naruto Nov 05 '14 at 12:19
@Naruto: He is not the OP. Could be hard trying to accept it ;) – juergen d Nov 05 '14 at 12:20
@juergend Excellent..! I found 1 issue please clarify. If I write for the month december as `sum(...) as Dec` it gives error but if I wrote as `sum(...) as Dece` it gives o/p. – Gowri Nov 05 '14 at 12:28
@Gowri: I updated the answer and escaped `Dec` with backticks. `DEC` is a [reserved-word in MySQL](http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html) and if you want to use it - you need to escape it. – juergen d Nov 05 '14 at 12:30
Given that this question is tagged with PHP, this is a silly solution. – Strawberry Nov 06 '14 at 10:52

score 0 · Answer 2 · answered Nov 05 '14 at 12:19

0

Try This..

Select Name,SUM(Total),
SUM(case when Month = 'Jan' then total else 0 end ) as Jan,
SUM(case when Month = 'Feb' then total else 0 end ) as Feb
from tbl_total
group by Name

Repeat the Case statement with different Months as required.

answered Nov 05 '14 at 12:19

Bilal Rao

154
8

Select row that has similar name

2 Answers2