MySQL SUM when using GROUP BY not working

Question

Let's say we have this table:

Symbol | Size
A      | 12
B      | 5
A      | 3
A      | 6
B      | 8

And we want a view like this:

Symbol | Size
A      | 21
B      | 13

So we use this:

Select Symbol, sum(Size) from table group by Symbol order by Symbol ASC

But instead we get this:

Symbol | Size
A      | 12
B      | 5

What am I doing wrong?!

Answer 1

You are doing it right, you should expect the correct results. Could you please supply more information about the DB you are using, additional schemas, etc?

Maybe you have some unique index on Symbol?

Try to execute the following to "sanity-test" your system:

SELECT SUM(Size) FROM table

Should result in 34

SELECT Symbol, Count(*) FROM table GROUP BY Symbol

Should results in 3 and 2

If both of the above work perfectly as you noted, please try:

SELECT Symbol, Count(*), Sum(Size) FROM table GROUP BY Symbol

This is your code, with the additions of Count(*) and without the ORDER BY clause. If that does not work after the two above do, I'm really puzzled...

Answer 2

I found out that somewhere in the Select commands that leaded to the Un-SUMable table instead of a left join there was a simple join.Although I still don't get why that should mess up the calculation, I changed that and now it works... I'm sorry I couldn't upload the whole thing...