sql - 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)

标签： sqlsql-servertsqlgroup-by

Answer 1

尝试

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