SQL计算组内不同值的和和数

Question

我想计算一下

(1)销售总额

(2)每种产品的不同商店数目

在一个查询中，如果可能的话。假设我们有数据：

+-----------+---------+-------+--------+
|   store   | product | month | amount |
+-----------+---------+-------+--------+
| Anthill   | A       |     1 |      1 |
| Anthill   | A       |     2 |      1 |
| Anthill   | A       |     3 |      1 |
| Beetle    | A       |     1 |      1 |
| Beetle    | A       |     3 |      1 |
| Cockroach | A       |     1 |      1 |
| Cockroach | A       |     2 |      1 |
| Cockroach | A       |     3 |      1 |
| Anthill   | B       |     1 |      1 |
| Beetle    | B       |     2 |      1 |
| Cockroach | B       |     3 |      1 |
+-----------+---------+-------+--------+

我尝试过，但没有运气：

select 
 [product]
,[month]
,[amount]
,cnt_distinct_stores = count(distinct(stores))
from dbo.temp
group by 
 [product]
,[month]
order by 1,2

是否有可能将GROUP BY子句与像SUM(amount) OVER(partition by [product],[month] ORDER BY [month] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)这样的窗口函数组合起来？

Answer 1

试一试

SELECT product,
       SUM(amount),
       COUNT(DISTINCT store)
FROM dbo.temp
GROUP BY product