CODEWITHSUNDEEP
mysql
Chyngyz Sydykov
Chyngyz Sydykov

Reputation: 470

Group and get result in one row according to some field value

Not long ago I was given a task to implement report. The problem that i am facing is the following. I have a table:

name      value   type
------------------------
100 Kasa  | 50000 |  B
100 Kasa  | 30000 |  A
 80 Kasa  |  500  |  A
 80 Kasa  |  50   |  B
200 Hello | 3000  |  A
645 Bye   | 1234  |  B

What I want to achieve is group by name, sum values if type is same and if type is different then write it into another cell. I have only 2 types.

I want to get result as

name       valueB    valueA   
--------------------------
100 Kasa  | 50000 |  30000
 80 Kasa  |   0   |   550
200 Hello |   0   |   3000
645 Bye   | 1234  |    0

For now, I am grouping them in php, which makes a mess in code, and now i am hoping that someone can help me to do it with mysql

Upvotes: 2

Views: 81

Answers (2)

Himanshu
Himanshu

Reputation: 32612

I think your expected output (result) is wrong
It should be:

NAME        VALUEB  VALUEA
--------------------------
100 Kasa    50000   30000
200 Hello   0       3000
645 Bye     1234    0
80 Kasa     50      500

You can use SUM function for that

With CASE statement:

SELECT name,
       SUM(CASE type WHEN 'B'THEN value ELSE 0 END) valueB,
       SUM(CASE type WHEN 'A' THEN value ELSE 0 END) valueA
FROM myTable
GROUP BY name;

Or with IF statement:

SELECT name,
       SUM(IF (type = 'B' , value , 0 )) valueB,
       SUM(IF (type = 'A' , value , 0 )) valueA
FROM myTable
GROUP BY name;

See this SQLFiddle

If your expected output is like this (NULL instead of 0)

SELECT name,
       GROUP_CONCAT(CASE type WHEN 'B'THEN value ELSE NULL END) valueB,
       GROUP_CONCAT(CASE type WHEN 'A' THEN value ELSE NULL END) valueA
FROM myTable
GROUP BY name;

See this SQLFiddle

Upvotes: 2

Barmar
Barmar

Reputation: 782693

SELECT name, SUM(IF(type = 'A', value, 0)) valueA, SUM(IF(type = 'B', value, 0)) valueB
FROM mytable
GROUP BY name

Upvotes: 0

Related Questions