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

Question

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)

You have `SELECT * FROM facebook_accts`. I feel certain you mean `SELECT COUNT(friend_count) FROM facebook_accts` — Smandoli, Aug 03 '12 at 13:21
You have 4 records with no friend count at all (not even zero). So the average is computed as `440728/(1128-4)`. — David Schwartz, Aug 03 '12 at 13:22
friend_count is nullable and @Smandoli I count the number of returned rows.. — Chris Mccabe, Aug 03 '12 at 14:27

score 1 · Accepted Answer · edited May 23 '17 at 11:55

1

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

edited May 23 '17 at 11:55

Community

1
1

answered Aug 03 '12 at 13:22

Omesh

27,801
6
42
51

is there a way to include these in the avg function? – Chris Mccabe Aug 03 '12 at 14:30
`IFNULL(0,SUM(friend_count))` ... assuming your nulls SHOULD be taken as zero. – Smandoli Aug 03 '12 at 14:57
`AVG(ISNULL(friend_count),0)` – Justin Swartsel Aug 03 '12 at 14:59

score 0 · Answer 2 · answered Aug 03 '12 at 13:20

0

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

answered Aug 03 '12 at 13:20

Prinzhorn

22,120
7
61
65

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

2 Answers2