CODEWITHSUNDEEP
mysqldatabasesum
aditseng
aditseng

Reputation: 99

MySQL sum giving different values

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?

Upvotes: 0

Views: 118

Answers (2)

spencer7593
spencer7593

Reputation: 108500

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.)

Upvotes: 2

Alain Collins
Alain Collins

Reputation: 16362

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

Upvotes: -1

Related Questions