MySql Sum and Count for simple table

Question

Could you help me with simple table SUM and COUNT calculating?

I've simple table 'test'

id name value
1  a    4
2  a    5
3  b    3
4  b    7
5  b    1

I need calculate SUM and Count for "a" and "b". I try this sql request:

SELECT name, SUM( value ) AS val, COUNT( * ) AS count FROM  `test`

result:

name val count
a    20  5

But should be

name val count
a    9   2
b    11  3

Could you help me with correct sql request?

Answer 1

You need group by

select 
name, 
sum(value) as value, 
count(*) as `count` 
from test group by name ;

Answer 2

Add GROUP BY. That will cause the query to return a count and sum per group you defined (in this case, per name).

Without GROUP BY you just get the totals and any of the names (in your case 'a', but if could just as well have been 'b').

SELECT name, SUM( value ) AS val, COUNT( * ) AS count 
FROM  `test` 
GROUP BY name