CODEWITHSUNDEEP
mysql
David
David

Reputation: 325

Query to concat data from multiple rows

Assuming I have the following data:

id     PGroup   Number
103     AAA         9946
103     BBB         8847
103     CCC         7260
103     DDD         1531
103     EEE         473
103     FFF         106
111     BBB         15353
111     AAA         15243
111     CCC         7615
111     DDD         2111
111     EEE         930
111     GGG         807
111     HHH         123
113     BBB         15585
113     AAA         12714
113     CCC         4343
113     DDD         2561
113     EEE         1063
113     GGG         406
113     HHH         69
123     AAA         11448
123     BBB         9560

For each ID, I would like to get the sum of AAA + BBB. Is that one-query-able?

103      AAA+BBB    18793
103     CCC         7260
103     DDD         1531
103     EEE         473
103     FFF         106
111     AAA+BBB     30596
111     CCC         7615
111     DDD         2111
111     EEE         930
111     GGG         807
111     HHH         123

Upvotes: 1

Views: 101

Answers (2)

Ike Walker
Ike Walker

Reputation: 65537

You can use a CASE statement to combine the AAA and BBB values into a single row, like this:

select id as id,
  (case when PGroup in ('AAA','BBB') then 'AAA+BBB' else PGroup end) as super_group,
  sum(number) as total
from your_table
group by id,super_group

Upvotes: 1

bfavaretto
bfavaretto

Reputation: 71908

GROUP_CONCAT would allow you to do something like that, if you accept leaving out rows where PGroup is not 'AAA' or 'BBB':

SELECT
    id, 
    GROUP_CONCAT(PGroup SEPARATOR '+'),
    SUM(Number)
FORM tbl
WHERE PGroup IN ('AAA', 'BBB')
GROUP BY id

Upvotes: 1

Related Questions