Mysql SUM with case statement

Question

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.

Shef · Accepted Answer · 2011-07-29 19:34:33Z

33

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;

answered Jul 29, 2011 at 19:34

Shef

45.6k15 gold badges82 silver badges91 bronze badges

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

3 Comments

Bill Karwin Over a year ago

Shorter, yes, but the IF() syntax is not ANSI SQL. Using CASE increases the chance of being portable, for what that's worth.

Shef Over a year ago

@Bill: Yes, you are right. I totally agree with you. However, there are so many things in MySQL, which are not ANSI SQL, that will not make SQL code written for MySQL that portable by default. Chances are when portability comes to question all the queries will be inspected, but it's great that you brought it up, thanks. :)

Bill Karwin Over a year ago

Yes, I agree portability is elusive, and not always worth it.

sll · Accepted Answer · 2011-07-29 19:49:45Z

7

Why not just to filter out items?

SELECT 
    SUM(ISNULL(percent, 0)) 
FROM 
    phppos_items_taxes
WHERE 
    cumulative = 1

In your case each row will be selected and CASE statement has to be applied, I believe with WHERE filter it would be significantly faster because WHERE clause executing before SELECT clause

edited Jul 29, 2011 at 19:49

answered Jul 29, 2011 at 19:39

sll

62.7k22 gold badges109 silver badges157 bronze badges

1 Comment

Shef Over a year ago

@Chris Muench: If the query does not involve other factors, use this solution, it is much faster indeed. :)

Collectives™ on Stack Overflow

Mysql SUM with case statement

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related