MySQL SUM multiple columns in same ROW

Question

+---------------+---------+-----------------+---------+ 
| product_count | size1   | sproduct_count2 | size2
+---------------+---------+-----------------+---------+ 
|            13 | 2x4     |               5 |     2x6 
|            14 | 2x6     |               2 |     4x8 
|            15 | 2x8     |               3 |     2x8 
|            16 | 4x4     |               2 |     4x4 
|            17 | 4x8     |              15 |     4x8
+---------------+---------+-----------------+---------+

How do I get the total counts so the return results appear like:

product_count | size
           13 | 2x4
           19 | 2x6
           18 | 2x8
           18 | 4x4
           34 | 4x8

I have tried:

SELECT SUM(product_count+product_count2) AS product_count, size1 FROM UNITS GROUP BY size1

and it works for some rows and others it does not work for. Would CONCAT be something I need to use?

@vivek_23 From my understanding, all product counts with each different size should be added together. For instance, there's 14 of 2x6 in product_count/size1, and there's 5 of 2x6 in sproduct_count2/size2. The sum, 19, is shown in the result next to 2x6. — slothiful
– slothiful, Commented Jun 26, 2019 at 14:55

forpas · Accepted Answer · 2019-06-26 14:44:31Z

3

You need to aggregate on the union of the 2 columns:

select sum(t.product_count) product_count, t.size from (
  select product_count, size1 size from units
  union all
  select sproduct_count2, size2 from units
) t
group by t.size

See the demo.
Results:

| product_count | size |
| ------------- | ---- |
| 13            | 2x4  |
| 19            | 2x6  |
| 18            | 2x8  |
| 18            | 4x4  |
| 34            | 4x8  |

edited Jun 26, 2019 at 14:44

answered Jun 26, 2019 at 14:36

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

mighty_dev · Accepted Answer · 2019-06-26 15:17:30Z

0

select u1.product_count + if(u2.product_count is null,0,u2.product_count) as product_count,u1.size1 as size
from units u1
left join (select sum(sproduct_count2) as product_count,size2 
from units
group by size2) u2
on u1.size1 = u2.size2;

Demo: https://www.db-fiddle.com/f/8TfoNen5toVHvy4DbBe7M6/0

You could first sum up all counts from sproduct_count2 with size2 with a group by on size2.
You could then do a left join on the above result with the units table itself with an additional if condition check if in case you find a size1 not there in size2(in which case you could add a 0).

Note: This only filters values appearing in size1(with it's appropriate counts) looking at the query you tried (SUM(product_count+product_count2) AS product_count, size1 FROM)

edited Jun 26, 2019 at 15:17

answered Jun 26, 2019 at 15:05

mighty_dev

17.8k3 gold badges24 silver badges39 bronze badges

Collectives™ on Stack Overflow

MySQL SUM multiple columns in same ROW

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related