计算总和的SQL Server查询

Question

我试图写一个SQL查询计算总和没有成功。

假设我们有:

• 表A，列id和type
• 表B，列id,a_id(与表A的关系)和amount

我成功地通过type计算记录数，如下例所示:

SELECT DISTINCT
type,
COUNT(A.id) OVER (PARTITION BY type) AS numOfRecords
FROM A;

如何计算每个type的金额总和(对A中所有不同类型的B表中的所有金额求和)?

Answer 1

您的查询通常写成:

select type, count(*) as num_records
from A
group by type;

然后，您可以将b合并为:

select a.type, count(*) as num_records, sum(b.amount)
from A left join
(select a_id, sum(amount) as amount
from b
group by a_id
) b
on b.a_id = a.id
group by a.type;

您也可以不使用子查询join和aggregate，但这会导致计数错误。要解决这个问题，可以使用count(distinct):

select a.type, count(distinct a.id) as num_records, sum(b.amount)
from A left join
from b
on b.a_id = a.id
group by a.type;