SQL将每月平均值并排放在列中

Question

我有两个查询，用于计算旧折扣和新折扣之间的平均值。我正在计算每月的平均值，我想知道如何将这些值并排列:

这是我的查询:

Select AVG(ABS(NewDiscount - OldDiscount)) AS [Average Discount Change in February]
From DiscountChange
where MONTH(ChangeDate) = 2
and YEAR(ChangeDate) = 2021
Select AVG(ABS(NewDiscount - OldDiscount)) AS [Average Discount Change in January]
From DiscountChange
where MONTH(ChangeDate) = 1
and YEAR(ChangeDate) = 2021

我想得到的输出是:

[Average Discount Change in February] | [Average Discount Change in January]

下面是DiscountChange表的模式:

Create Table DiscountChange
(
DiscountChangeId Decimal(12) NOT NULL PRIMARY KEY
, CouponId Decimal(12) NOT NULL FOREIGN KEY REFERENCES Coupon(CouponId)
, OldDiscount MONEY NOT NULL
, NewDiscount MONEY NOT NULL
, ChangeDate DATE NOT NULL 
)

我如何修改我的查询来实现如上的输出?

Thanks in advance

Answer 1

您可以使用条件聚合:

Select AVG(CASE WHEN MONTH(ChangeDate) = 1 THEN ABS(NewDiscount - OldDiscount) END),
AVG(CASE WHEN MONTH(ChangeDate) = 2 THEN ABS(NewDiscount - OldDiscount) END)      
From DiscountChange
where ChangeDate >= '2021-01-01' AND
ChangeDate < '2021-03-01'