Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Mysql SUM with case statement

Tags:

sql

mysql

case

sum

SELECT 
    SUM(
        CASE 
           WHEN cumulative = 1 
           THEN percent 
           ELSE 0 
        END) 
FROM phppos_items_taxes;

Given the above statement does this do the following:

mysql> select * FROM phppos_items_taxes;
+---------+-----------+---------+------------+
| item_id | name      | percent | cumulative |
+---------+-----------+---------+------------+
|       1 | Tax 1     |    8.00 |          0 |
|       1 | Tax 2     |   10.00 |          1 |
|       3 | Sales Tax |    8.00 |          0 |
|       4 | Tax 1     |   20.00 |          0 |
|       4 | Tax 2     |   20.00 |          0 |
+---------+-----------+---------+------------+

Does this SUM up percent for each row that cumulative = 1. If cumulative != 1 then 0 is summed.

like image 661
Chris Muench Avatar asked Jul 29 '11 19:07

Chris Muench


People also ask

How do you use case when using sum?

Then comes the curious use of a SUM() with a CASE WHEN . This expression says whenever the number_of_lectures is higher than 20, the row is assigned the value 1. If the condition is not met, the assigned value is 0. The SUM() function will sum all those rows that have the assigned value equal to 1.

Does MySQL support case statements?

The MySQL CASE Statement The CASE statement goes through conditions and returns a value when the first condition is met (like an if-then-else statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.

Can we use aggregate function in case statement?

CASE statement in SQL and aggregate functions Aggregate functions in SQL Server perform calculations and return a single value. Examples of aggregate functions are MIN, MAX, COUNT, ABG and CHECKSUM. For this purpose, we use the COUNT aggregate function in SQL Server.

How do you add a case statement in SQL?

Use Case 1: Simple SQL SUM Function To get the value, you just need to add the salary of all employees. SUM () will make your life easy, providing such reports quickly. If you have any NULL value in the column, this function will ignore it and proceed with the next row value.


1 Answers

Yes it does! A shorter and cleaner query (IMHO) would be to use IF statement:

SELECT SUM(IF(cumulative = 1, `percent`, 0)) FROM phppos_items_taxes;
like image 105
Shef Avatar answered Sep 28 '22 00:09

Shef