To count or not to count is a question

Question

I have this table:

Strawberries 2 Strawberries 3 Strawberries 4 Chocolate 3 Chocolate 4 Chocolate 5 Chocolate 6 Apples 3 Apples 4 Apples 5 Apples 6 

My idea is to get the number of items and the total number of items apiece, for example:

 Item Number Total Strawberries 2 9 Strawberries 3 9 Strawberries 4 9 Chocolate 3 18 Chocolate 4 18 Chocolate 5 18 Chocolate 6 18 Apples 3 18 Apples 4 18 Apples 5 18 Apples 6 18 

So I do:

 SELECT TOP (100) PERCENT item, number, COUNT(number) AS total FROM products GROUP BY item, number ORDER BY item 

But the numbers I get are wrong:

 Item Number Total Strawberries 2 6 Strawberries 3 21 Strawberries 4 17 Chocolate 3 1 Chocolate 4 8 Chocolate 5 34 Chocolate 6 1 Apples 3 1 Apples 4 10 Apples 5 32 Apples 6 1 

What am I doing wrong?

Answer 1

 SELECT item, number, SUM(number) OVER (PARTITION BY item) FROM products ORDER BY item, number