MySQL sum giving different values

Question

I have a table with a list of payments. When I try to retrieve the total I get different values depending on which columns I use in the SUM().

Here is my query:

SELECT 
sum(Email_Broadcasting+Custom_Email_Newsletter+Digital_Newsletter+Mortgage_Matters) as EM,
sum(Resident_Lists+Consumer_Lists+Business_Lists+Email_Lists+Specialty_Lists) as DL,
sum(Database_Services+Email_Appending+Email_Cleansing) as DS,
sum(Email_Broadcasting+Custom_Email_Newsletter+Digital_Newsletter+Mortgage_Matters
+Resident_Lists+Consumer_Lists+Business_Lists+Email_Lists+Specialty_Lists
+Database_Services+Email_Appending+Email_Cleansing) as Total
FROM payment_orders

As you can see Total should be equal to EM+DL+DS, but this is the value I get instead:

EM           DL         DS  Total
66122.79    772030.36   55403.67    328701.27

which doesn't really add up.

Am I doing something wrong?

Please show your data structure and sample data and your expected output. — Himanshu
– Himanshu, Commented Jul 11, 2013 at 4:06

spencer7593 · Accepted Answer · 2013-07-11 04:49:09Z

2

The most likely explanation is NULL values. Some of the values in some of those columns are probably NULL.

e.g.

4 + 5 + 0     =>  9 

4 + 5 + NULL =>  NULL

If you want to handle a NULL value like a 0, then wrap every column reference in a function that tests for NULL and returns a zero if the value is NULL.

The MySQL IFNULL() function is one way to do this:

IFNULL(4,0) + IFNULL(5,0) + IFNULL(NULL,0) =>  9

e.g.

SELECT SUM(IFNULL( Email_Broadcasting      ,0)
          +IFNULL( Custom_Email_Newsletter ,0)
          +IFNULL( Digital_Newsletter      ,0)
          +IFNULL( Mortgage_Matters        ,0) 
          ) as EM,

(I didn't verify that all of the columns from each of the subtotals is included in the total... that's obviously going to be a source of a problem, but I'm assuming that you've already checked that.)

answered Jul 11, 2013 at 4:49

spencer7593

109k15 gold badges122 silver badges148 bronze badges

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

Comments

Alain Collins · Accepted Answer · 2013-07-11 04:39:07Z

-1

You have a null value. sum() is not null safe.

answered Jul 11, 2013 at 4:39

Alain Collins

16.4k2 gold badges34 silver badges56 bronze badges

1 Comment

spencer7593 Over a year ago

The MySQL SUM() aggregate function is "null safe", in that a NULL value will be ignored, and what will be returned is a sum of the non-NULL values. It's the addition operation (a+b+c) that is not "null safe".

Collectives™ on Stack Overflow

MySQL sum giving different values

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related