如何在一个查询中对所有联合进行求和 [英] How to sum all the unions iin one single query

问题描述

[ ^ ] [ ^ ]请在下面查询查询

[^][^]Please find below query

select 'Closed' as 'Medium',
sum(case when Medium='Application' then 1 else 0 end) 'Application',
sum(case when Medium='E-mail' then 1 else 0 end) 'E-mail',
sum(case when Medium='Phone' then 1 else 0 end) 'Phone',
sum(case when Medium='Visit' then 1 else 0 end) 'Visit',
sum(case when Medium='Web' then 1 else 0 end) 'Web',
sum(case when Medium='Application' then 1 else 0 end)+
sum(case when Medium='E-mail' then 1 else 0 end)+
sum(case when Medium='Phone' then 1 else 0 end)+
sum(case when Medium='Visit' then 1 else 0 end)+
sum(case when Medium='Web' then 1 else 0 end) as 'Total'
from im_ticketmaster where status='Closed'
union 
select 'Resolved' as 'Medium',
sum(case when Medium='Application' then 1 else 0 end) 'Application',
sum(case when Medium='E-mail' then 1 else 0 end) 'E-mail',
sum(case when Medium='Phone' then 1 else 0 end) 'Phone',
sum(case when Medium='Visit' then 1 else 0 end) 'Visit',
sum(case when Medium='Web' then 1 else 0 end) 'Web',
sum(case when Medium='Application' then 1 else 0 end)+
sum(case when Medium='E-mail' then 1 else 0 end)+
sum(case when Medium='Phone' then 1 else 0 end)+
sum(case when Medium='Visit' then 1 else 0 end)+
sum(case when Medium='Web' then 1 else 0 end) as 'Total'
from im_ticketmaster where status='Resolved'
union
select 'Pending' as 'Medium',
sum(case when Medium='Application' then 1 else 0 end) 'Application',
sum(case when Medium='E-mail' then 1 else 0 end) 'E-mail',
sum(case when Medium='Phone' then 1 else 0 end) 'Phone',
sum(case when Medium='Visit' then 1 else 0 end) 'Visit',
sum(case when Medium='Web' then 1 else 0 end) 'Web',
sum(case when Medium='Application' then 1 else 0 end)+
sum(case when Medium='E-mail' then 1 else 0 end)+
sum(case when Medium='Phone' then 1 else 0 end)+
sum(case when Medium='Visit' then 1 else 0 end)+
sum(case when Medium='Web' then 1 else 0 end) as 'Total'
from im_ticketmaster where status='Pending'

以上查询输出是

for the above query output is

Medium  Application     E-mail  Phone   Visit   Web     Total
Closed	        0       0       0       0       1       1
Pending	        0       0       2       0       4       6
Resolved        2       0       36      0       489     527

但需要输出如下

but need the output like below

Medium  Application     E-mail  Phone   Visit   Web     Total
Closed	        0       0       0       0       1       1
Pending	        0       0       2       0       4       6
Resolved        2       0       36      0       489     527
Grand Total     2       0       38      0       494	

推荐答案

你需要 pivot [ ^ ]数据！



试试这个：

You need to pivot[^] data!

Try this:

SELECT [status], [Application], [E-mail], [Phone], [Visit], [Web], [Application] + [E-mail] + [Phone] + [Visit] + [Web] AS [Total]
FROM (
    SELECT [status], Medium
    FROM im_ticketmaster
    UNION ALL
    SELECT 'Total' AS [status], Medium
    FROM im_ticketmaster
    ) AS DT
PIVOT(COUNT([Medium]) FOR [status] IN ( [Application], [E-mail], [Phone], [Visit], [Web])) AS PT

更多内容来自CP KnowlegdeBase：

在SQL查询中使用Pivot的简单方法 [ ^ ]

简化DataTable简化 [ ^ ]

C＃数据透视表 [ ^ ]

使用Pivot和UnPivot转换 [ ^ ]

如何在T-SQL中执行数据透视操作..？ [ ^ ]

简单&使用C＃和ASP.NET的高级Pivots [ ^ ]

SQL - 带有总列和行的枢轴 [ ^ ]

More at CP KnowlegdeBase:
Simple Way To Use Pivot In SQL Query[^]
Pivoting DataTable Simplified[^]
C# Pivot Table[^]
Working with Pivot and UnPivot Transformation[^]
How to Perform Pivot Operation in T-SQL..?[^]
Simple & Advanced Pivots with C# and ASP.NET[^]
SQL - Pivot with Grand Total Column and Row[^]

这篇关于如何在一个查询中对所有联合进行求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！