mysql fetch result group by column name

Question

this is my tbl_client:

c_ID    city       type         amount
----    --------   ----------   ------
1       Boston     Medical      1500
2       Miami      Educational  3000
3       Dallas     Scholarship  1000
4       Boston     Scholarship  1500
5       Miami      Medical      3000
6       Boston     Educational  1000
7       Miami      Medical      3000
8       Dallas     Medical      1000

how to fetch the result as this:

city          Medical       Educational    Scholarship
-----------   -----------   ------------   -----------
Boston        1500          1000           1500     
Dallas        1000          Null           1000     
Miami         6000          3000           Null

score 0 · Accepted Answer · answered Dec 09 '18 at 07:21

You can try to use condition aggravated function SUM with GROUP BY

Schema (MySQL v5.7)

CREATE TABLE T(
   c_ID int,
   city varchar(50),
   type varchar(50),
   amount int
);




INSERT INTO T VALUES (1,'Boston','Medical',1500);
INSERT INTO T VALUES (2,'Miami','Educational',3000);
INSERT INTO T VALUES (3,'Dallas','Scholarship',1000);
INSERT INTO T VALUES (4,'Boston','Scholarship',1500);
INSERT INTO T VALUES (5,'Miami','Medical',3000);
INSERT INTO T VALUES (6,'Boston','Educational',1000);
INSERT INTO T VALUES (7,'Miami','Medical',3000);
INSERT INTO T VALUES (8,'Dallas','Medical',1000);

Query #1

SELECT city,          
    SUM(CASE WHEN type = 'Medical' THEN amount END) Medical,  
    SUM(CASE WHEN type = 'Educational' THEN amount END) Educational,   
    SUM(CASE WHEN type = 'Scholarship' THEN amount END) Scholarship   
FROM T 
GROUP BY city;

| city   | Medical | Educational | Scholarship |
| ------ | ------- | ----------- | ----------- |
| Boston | 1500    | 1000        | 1500        |
| Dallas | 1000    |             | 1000        |
| Miami  | 6000    | 3000        |             |

View on DB Fiddle

@UmandapJervy You can try to add `ELSE 0` https://www.db-fiddle.com/f/5YRRuvux8pEAbYXJJdhWBj/1 — D-Shih, Dec 09 '18 at 09:29

score 0 · Answer 2 · answered Dec 09 '18 at 07:25

0

SELECT city
    CASE        
     WHEN type = 'Medical' THEN amount END) Medical,  
     WHEN type = 'Educational' THEN amount END) Educational,   
     WHEN type = 'Scholarship' THEN amount END) Scholarship   
FROM tbl_client
GROUP BY city

answered Dec 09 '18 at 07:25

Ferrakkem Bhuiyan

2,741
2
22
38

mysql fetch result group by column name

2 Answers2