I have this query which is working fine as expected but now I want to add a grand total and I am not sure where to add both the column A and B. Can someone please help!!
我有这个查询,按预期工作正常,但现在我想添加一个总计,我不知道在哪里添加列A和B.可以有人请帮助!
select *
from
(
select f_Parameter, _company_code,ISNULL(Convert(numeric(18,2),f_value),0) as f_value ,f_Sort_Order ,convert(varchar(11), f_Mis_day,103) as f_Mis_day
from TEST with(NOLOCK) where convert(date, f_Mis_day,103) = CONVERT(date,getdate()-1,103)
) src
pivot
(
SUM(f_value)
for f_company_code in ([A], [B])
) piv order by f_Sort_Order;
Output:
f_Parameter f_Sort_Order f_Mis_day A B
------------------------------------------------------------------------
Consumption Amount in INR 1 26/02/2018 10925.80 24495.10
Transaction Count 2 26/02/2018 5.00 9.00
Expected Output:
f_Parameter f_Sort_Order f_Mis_day A B Total
--------------------------------------------------------------------------------------------
Consumption Amount in INR 1 26/02/2018 10925.80 24495.10 35420.90
Transaction Count 2 26/02/2018 5.00 9.00 14.00
1 个解决方案
#1
0
As you are looking only for few company codes then you could use conditional aggregation approach.
由于您只查找少数公司代码,因此您可以使用条件聚合方法。
Declare @date date = cast(dateadd(day, -1, getdate()) as date)
SELECT
f_Parameter, f_Mis_day,
SUM(CASE WHEN f_company_code = 'A' THEN f_value END) [A],
SUM(CASE WHEN f_company_code = 'B' THEN f_value END) [B],
SUM(f_value) Total
FROM table t
WHERE f_Mis_day = @date
GROUP BY f_Parameter, f_Mis_day
#1
0
As you are looking only for few company codes then you could use conditional aggregation approach.
由于您只查找少数公司代码,因此您可以使用条件聚合方法。
Declare @date date = cast(dateadd(day, -1, getdate()) as date)
SELECT
f_Parameter, f_Mis_day,
SUM(CASE WHEN f_company_code = 'A' THEN f_value END) [A],
SUM(CASE WHEN f_company_code = 'B' THEN f_value END) [B],
SUM(f_value) Total
FROM table t
WHERE f_Mis_day = @date
GROUP BY f_Parameter, f_Mis_day