CODEWITHSUNDEEP
mysqlsqlgroup-bysum
B Seven
B Seven

Reputation: 45943

How could SUM(column) give incorrect results in MySQL?

I have the following simple MySQL query, called from PHP:

SELECT foo_id, SUM(number_of_guests)
FROM guests 
WHERE foo_id = $foo_id
GROUP BY foo_id

This works fine, except for one $foo_id, which returns about 2.5 times greater than the sum of the number_of_guests field.

What could cause this behavior for only a certain value of $foo_id?

Is there a better way to do this?

Upvotes: 0

Views: 331

Answers (2)

B Seven
B Seven

Reputation: 45943

The problem is that there was one row with a large value for number_of_guests. I didn't see in in browsing the data because there are a few hundred rows. It didn't show up when I copied and pasted from HTML page into Excel because that row was missing most of the other columns, and the HTML page has all the columns.

Thanks for all your help!

Upvotes: 0

Mark Byers
Mark Byers

Reputation: 838416

Your query should work. The error is most likely in the other method you are using to verify the result.

Is there a better way to do this?

Yes. Since you are only fetching one group there's no need for your GROUP BY clause:

SELECT SUM(number_of_guests)
FROM guests 
WHERE foo_id = $foo_id

Upvotes: 1

Related Questions