How to use MySql SUM() on multiple columns

Question

I am struggling with MySql SUM() function . For example , I have a table called my_table with columns('id','price_1','price_2') . I know this table is not following 1st normalization . But I need to just to demonstrate what I am in need . I want to select like this .

SELECT SUM({price_1 if price_2 is null}) GROUP BY `id`

How can I achieve this?

Could you please clarify whether you need sum of price_2 from only those rows where price_1 is null or whether you need to have sum of from all rows so that price_1 is used unless price_2 is non-null, in which case it is to be included in sum? — vhu
– vhu, Commented Jul 15, 2015 at 9:53

Raging Bull · Accepted Answer · 2015-07-15 10:25:28Z

2

Use COALESCE:

SELECT SUM(COALESCE(price_2,price_1)) as TotalPrice
FROM my_table 
GROUP BY `id`

COALESCE will take price_1 if price_2 is NULL. i.e., COALESCE will return the first parameter which is not NULL.

Sample result in SQL Fiddle

If you want to find the sum of price_1 when all the price_2 are NULL. Then:

SELECT COALESCE(SUM(price_2),SUM(price_1)) as TotalPrice
FROM my_table 
GROUP BY `id`

Sample result in SQL Fiddle

edited Jul 15, 2015 at 10:25

answered Jul 15, 2015 at 9:50

Raging Bull

18.8k13 gold badges52 silver badges56 bronze badges

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

Collectives™ on Stack Overflow

How to use MySql SUM() on multiple columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related