CODEWITHSUNDEEP
mysqlsql
TheRunner565
TheRunner565

Reputation: 99

Mysql Sum Conditional

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

Upvotes: 1

Views: 59

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 176189

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;

Upvotes: 1

Related Questions