sum() returning wrong result in MySQL

PixelPixie

I can't get the sum() function in MySQL to work properly - it works but it constantly returns the wrong result - it seems to be adding the number of rows to the sum result, which is very strange to me! I've checked my code numerous times as well as scouring the web for answers in the last few days but have come up with zilch.

An excerpt of the table is as follows:
+----+

---

+

---

+

---

+
| id | code | rating | status
+

---

+

---

+
| 1 | AB1 2 OK | no |
| 2 | AB1 3 OK | no |
| 3 | AB1 4 OK | no |
| 4 | AB2 5 OK | no |
| 5 | AB1 2 OK | no |
+----+

---

+

---

+

---

+-

Now I want to get the total of the ratings for AB1 (which comes to 11)

select sum(rating) as total from ratings where code ='AB1';

This gives me 15 and not 11!

select sum(rating) as total from ratings where code='AB2';

should give me 5 but instead gives me 6!
It seems to be adding the number of rows to the addition result - 11+4=15 and 5+1=6.
Can anyone throw any light on this weird behaviour, or have I overlooked something essential?
I'm using MySQL 5 on the command line.

daymobrew

What do you get when you omit as total ?

PixelPixie

When I omit 'as total' I still get the same results. Believe me I've tried all sorts of combinations to no avail.

Ste.phen

I'd be nearly sure that the SUM() is working correctly, but your data isn't what you're expecting for some reason...

Webmonkey

Strange one - What data type are the columns

PixelPixie

The rating column is an enum ('NR','1','2','3','4','5'). Now that you mention it perhaps the 'NR' is causing the problem but then none of the rows selected are set to 'NR'

Webmonkey

PixelPixie wrote: »

The rating column is an enum ('NR','1','2','3','4','5'). Now that you mention it perhaps the 'NR' is causing the problem but then none of the rows selected are set to 'NR'

I would put my money on the fact that you are not actually adding the numbers but you are adding the enum positions. So 1 -> 2, 2->3 etc. (assuming that positions arn't 0 based.) This could be why you've an extra 1 for each row.

Bet if you placed 5, before 4, you'd end up with totally different results. Plus these arn't integers and I don't think MySQL will like using the sum function on them. Could be wrong though.

Webmonkey

From http://www.linuxtopia.org/online_books/database_guides/mysql_5.1_database_reference_guide/enum.html
...

MySQLDocs wrote:

Values from the list of allowable elements in the column specification are numbered beginning with 1.

Also, the values in the Enum that you enter are not actually stored per row. A pointer to index is stored to the location of the enums (Saves space).

Index
1 -> NR
2 -> 1
3 -> 2
4 -> 3
5 -> 4

So infact, it looks like you are adding the indexes as apposed to the values

PixelPixie

Aha! Thanks guys for your help. I'll change the datatype to smallint or tinyint and use '0' for 'NR' and see if that works.

Webmonkey

No Probs,

This from the docs too:

If you retrieve an ENUM value in a numeric context, the column value's index is returned

So you are definitely working on the index value. The index value of 0 is kept for the null enum where you don't specify a value.

PixelPixie

Webmonkey wrote: »

No Probs,

This from the docs too:


So you are definitely working on the index value. The index value of 0 is kept for the null enum where you don't specify a value.

Thanks Webmonkey! I missed that bit in the docs.
I've changed the datatype to tinyint and am happy to report that it is now returning the correct results.
Cheers

Webmonkey

Only glad to help