IF with SUM produces incorrect results in MySQL

Question

I am getting incorrect results when I try to insert a SUM into an IF clause. The results are correct when I use COUNT, either incorrect or NULL when I use SUM. I have been able to produce the correct results for each statement through another query (as a means of validating the formula). What is the syntax to get correct results for an SUM within an IF statement? Based on another StackOverflow question, I attempted to fix the formula, but it produced an error.

SELECT
IF (Artist LIKE '%Hillsong%' , 'Hillsong', NULL ) as Artist,

COUNT(IF(CCD BETWEEN 28 AND 730,1,NULL)) AS 1_CC,    -- result 191, which is correct 
IF(CCD BETWEEN 28 AND 730, SUM(CC28),NULL) AS 2_CC,  -- result NULL, should be 610   xx
COUNT(IF(CCD > 28,1,NULL)) AS 3_CC,                  -- result 684, which is correct 
COUNT(IF(CCD < 730,1,NULL)) AS 4_CC,                 -- result 502, which is correct 
IF(CCD > 28,SUM(CC28),NULL) AS 5_CC,                 -- result 2253, should be 1882  xx
--- SUM(IF(CCD > 28,CC28,NULL) AS 6_CC,              -- my attempt to fix, creates error
IF(CCD < 730,SUM(CC28),NULL) AS 7_CC                 -- result NULL, shoul be 981    xx

FROM praisecharts_reporting.large_sales_report
GROUP BY 1;

As a frame of reference, I am a music publisher, and I am trying to get results for all songs where the artist includes "Hillsong", where the Chord Chart (CC) has been available between 28 and 730 days (CCD BETWEEN 28 AND 730). The COUNT should tell me how many song titles qualify, and SUM should tell me the total unit sales for all songs that qualify.

Answer 1

I figured out the answer to my problem. The syntax for putting an IF clause inside a SUM is to use CASE WHEN. Below is the solution to my query above, and it produces correct results all around:

SELECT
IF (Artist LIKE '%Hillsong%' , 'Hillsong', NULL ) as Artist,

COUNT(IF(CCD BETWEEN 28 AND 730,1,NULL)) AS 1_CC,
SUM(CASE WHEN CCD BETWEEN 28 AND 730 THEN CC28 ELSE 0 END) AS 2_CC,
COUNT(IF(CCD > 28,1,NULL)) AS c3_CC,  
COUNT(IF(CCD < 730,1,NULL)) AS c4_CC, 
SUM(CASE WHEN CCD > 28 THEN CC28 ELSE 0 END) AS 5_CC,
SUM(CASE WHEN CCD < 730 THEN CC28 ELSE 0 END) AS 6_CC

FROM praisecharts_reporting.large_sales_report
GROUP BY 1;

Answer 2

You should be on the right track on your attempt to use SUM - IF

--- SUM(IF(CCD > 28,CC28,NULL) AS 6_CC,              -- my attempt to fix, creates error

If you look closely, you are missing an ending parenthesis. We must pay attention to the error we received. Most likely you received a syntax error right after AS 6_CC,.

Just add a closing parenthesis after NULL:

SUM(IF(CCD > 28,CC28,NULL)) AS 6_CC, 

Try to use SUM - IF on your other columns as well. Let me know if this works.

With SUM - IF you can have a true / false result, which is as simple as it can be (though you can have nested if's inside, but that would make it unreadable).

With SUM - CASE you have an option to have more results by providing more conditions, just like a SWITCH statement.

Answer 3

Without the underlying data it would be hard to verify the results. But your NULL result may be due to a underlying NULL in data or due to condition clause.

Try replacing SUM(CC28) with SUM(IFNULL(CC28,0))

A sum of Integer and NULL equals NULL. Hence you might be getting NULL in second set.