CODEWITHSUNDEEP
mysqlsql
user3239126
user3239126

Reputation:

different results for two queries

Query 1 :

SELECT  
                SUM(aol_int) AS AOL,
                SUM(android_phone_int) AS Android_Phone,
                SUM(androidTablet_int) AS Android_Tablet,
                SUM(apple_mail_int) AS Apple_Mail,
                SUM(blackberry_int) AS Blackberry,
                SUM(Eudora_int) AS Eudora,
                SUM(gmail_int) AS Gmail,
                SUM(hotmail_int) AS Hotmail,
                SUM(lotus_notes_int) AS Lotus_Notes,
                SUM(other_int) AS Other,
                SUM(other_webmail_int) as Other_Web_Mail,
                SUM(Outlook_int) AS Outlook,
                SUM(Postbox_int) AS Postbox,
                SUM(sparrow_int) AS Sparrow,
                SUM(thunderbird_int) AS Thunderbird,
                SUM(windowsLiveMail_int) AS Windows_Live_Mail,
                SUM(yahoo_int) AS Yahoo,
                SUM(iPad_int) AS iPad,
                SUM(iPhone_int) AS iPhone,
                SUM(iPod_int) AS iPod

       FROM    mytable;


Query 2: 

select sum(aol_int + android_phone_int + androidtablet_int+apple_mail_int+blackberry_int+Eudora_int+gmail_int+hotmail_int+lotus_notes_int+other_int+other_webmail_int+Outlook_int+Postbox_int+sparrow_int+thunderbird_int+windowsLiveMail_int+yahoo_int+iPad_int+iPhone_int+iPod_int) 
as total_percentage

FROM mytable;

When I am summing up the results of Query 1 I am getting different sum as compared to what I am getting via Query2. The value in Query2 is less than Query 1. Why is it like that?

TROUBLESHOOTING:

I tried to write my query like this:

SELECT SUM( ISNULL(aol_int,0)           + 
            ISNULL(android_phone_int,0) + 
            ISNULL(androidtablet_int,0) +
            ISNULL(apple_mail_int,0)    + 
            ISNULL(blackberry_int,0)    + 
            ISNULL(Eudora_int,0)        +
            ISNULL(gmail_int,0)         +
            ISNULL(hotmail_int,0)       +
            ISNULL(lotus_notes_int,0)   +
            ISNULL(other_int,0)         +
            ISNULL(other_webmail_int,0) +
            ISNULL(Outlook_int,0)       +
            ISNULL(Postbox_int,0)       +
            ISNULL(sparrow_int,0)       +
            ISNULL(thunderbird_int,0)   +
            ISNULL(windowsLiveMail_int,0)+
            ISNULL(yahoo_int,0)         +
            ISNULL(iPad_int,0)          +
            ISNULL(iPhone_int,0)        +
            ISNULL(iPod_int,0) )AS total_percentage

FROM mytable;

However, I am getting an error after running above query in the MySQL workbench:

Error Code: 1582. Incorrect parameter count in the call to native function 'ISNULL'. What is wrong here?

Upvotes: 0

Views: 65

Answers (1)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726539

This could happen if some columns in some of the rows contain nulls. When this happens, all columns in a row with even a single null column would produce null in the chain of additions, so the row will add nothing to the total.

Here is a short demo of this effect. Setup:

create table test(x int null, y int null);
insert into test(x,y) values (1,null);
insert into test(x,y) values (null,2);
insert into test(x,y) values (3,3);

Queries:

select sum(x+y) from test;     -- Shows 6

select sum(x)+sum(y) from test -- Shows 9

See this demo on sqlfiddle: link.

Upvotes: 2

Related Questions