CODEWITHSUNDEEP
mysqlsql
Chris Mccabe
Chris Mccabe

Reputation: 1951

Why does the total from my query results not add up?

I have three queries that get stats from the database, but the total does not add up correctly for my results. If I do the math myself this is what I get: // 440728 / 1128 = 390.72

However, the following is what is returned by my queries:

SELECT * FROM facebook_accts
         WHERE user_id IN (SELECT id FROM  `user_accts` WHERE owner_id = '121') 
// returns 1128

SELECT sum(friend_count) FROM facebook_accts
                         WHERE user_id IN
                            (SELECT id FROM  `user_accts` WHERE owner_id = '121') 
// returns 440728

SELECT avg(friend_count) FROM facebook_accts
                         WHERE user_id IN
                            (SELECT id FROM  `user_accts` WHERE owner_id = '121') 
// returns 392.11 (number formatted to two decimal places by php)

Upvotes: 2

Views: 158

Answers (2)

Omesh
Omesh

Reputation: 29081

this may be happening because of column friend_count having some NULL values because SUM and AVG sunctions ignore NULL values. see here.

Upvotes: 1

Prinzhorn
Prinzhorn

Reputation: 22508

I guess the 1128 rows contain NULL values (which AVG and SUM ignore).

Upvotes: 0

Related Questions