开发者

To count or not to count, that is the 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 of items per thing, like so:

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'm doing:

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 all 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  开发者_StackOverflow社区           10
Apples       5             32
Apples       6             1

What am i doing wrong?


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

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜