Mysql Sum Conditional

Question

I'm trying to get the total cost from the table below and if Include_Extra is set, use the value in the corresponding Extra_Seat_Cost column. I'm new to mysql, so any help would be greatly appreciated!

+-----------+-----------------+---------------+
| ID | Cost | Extra_Seat_Cost | Include_Extra |
+-----------+-----------------+---------------+
|  3 |   20 |      15         |       1       |
|  4 |   10 |      5          |       0       |
+----+------+-----------------+---------------+

The result should yield total = $45

Lukasz Szozda · Accepted Answer · 2015-11-22 21:57:06Z

1

You can use SUM with CASE WHEN:

SELECT SUM(Cost + CASE WHEN Include_Extra = 1   --if Include_Extra is bool delete = 1
                       THEN COALESCE(Extra_Seat_Cost,0) 
                       ELSE 0 END) AS total
FROM table_name;

SqlFiddleDemo

I've added COALESCE in case Extra_Seat_Cost can be nullable. number + NULL produces NULL.

If you have grouping column use:

SELECT group_column, SUM(Cost + CASE WHEN Include_Extra = 1 
                                     THEN COALESCE(Extra_Seat_Cost,0) 
                                     ELSE 0 END) AS total
FROM table_name;
GROUP BY group_column;

edited Nov 22, 2015 at 21:57

answered Nov 22, 2015 at 21:51

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

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

Collectives™ on Stack Overflow

Mysql Sum Conditional

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