MySQL SUM by if statement

Question

I have 3 columns in my MySQL table type, sum and custom_sum where custom_sum can be empty, and i am stuck on modifying query

SELECT `type`, SUM(`sum`) FROM `localhost-test` GROUP BY `type`

What i need is to use sum in SUM() if custom_sum is empty, and use custom_sum if it is not. Is it possible to sum by different columns based on "if" statement in mysql ?

What exactly you mean by saying "empty"? If it's NULL value, then use COALESCE as @Notolysses told below in his answer. If empty means 0 or "", then you need to use IF or CASE. Just don't do as user3414693 said, but enclose IF or CASE inside SUM(...). — DarkSide
– DarkSide, Commented Apr 22, 2014 at 8:12

suslov.nikita · Accepted Answer · 2014-04-22 08:19:54Z

3

If custom_sum value is null :

SELECT 
    `u.type`,
    SUM(COALESCE(`custom_sum`,`sum`))
FROM 
    `localhost-test` 
GROUP BY 
    `type`

If custom_sum value is empty (=='') :

 SELECT 
    `u.type`,
    SUM(CASE WHEN `custom_sum` = '' THEN `sum` ELSE `custom_sum` END)
FROM 
    `localhost-test` 
GROUP BY 
    `type`

edited Apr 22, 2014 at 8:19

answered Apr 22, 2014 at 8:02

suslov.nikita

44.7k11 gold badges92 silver badges113 bronze badges

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

2 Comments

Ronald Alexander Kailola Over a year ago

can use coalesce for empty string like NULL??

suslov.nikita Over a year ago

@Ronald Alexander Kailola : Thank you for a tip, I've updated my answer in case value is empty but not null.

Collectives™ on Stack Overflow

MySQL SUM by if statement

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related