CODEWITHSUNDEEP
sqlsql-server
Matt
Matt

Reputation: 15071

SQL Server Pivot as percentage rather than values and add a total row and column

I have code which gives me a small pivot:

SELECT [U C], [M] AS EE, [F] AS LL
FROM   
(SELECT [U N], f_k, [U C]  
FROM [MH2] 
WHERE [U C] IN ('I', 'E', 'T')) p  
PIVOT (COUNT ([U N]) 
 FOR f_k IN ([M], [F])) AS pvt  
ORDER BY pvt.[U C]

Output:

U C     EE      LL
E       509762  412358
I       16440   1138538
T       197     8753

However I want this to be a percentage like this and have a total row and column:

U C   EE        LL      Total
E     24.44%    19.77%  44.20%
I     0.79%     54.58%  55.37%
T     0.01%     0.42%   0.43%
Total 25.23%    74.77%  100.00%

Edit Solved using a variation of @Gordon Linoff's Answer.

SELECT COALESCE([U C], 'Total') [U C],
SUM(CASE WHEN f_k = 'F' THEN 1 ELSE 0 END) * 100.0 / cnt LL,
SUM(CASE WHEN f_k = 'M' THEN 1 ELSE 0 END) * 100.0 / cnt EE,
COUNT(*) * 100.0 / cnt Total
FROM (SELECT mh2.*, count(*) over () cnt
FROM mh2
WHERE [U C] IN ('I', 'E', 'T') 
AND f_k IN ('F', 'M')) mh2
GROUP BY GROUPING SETS (([U C], cnt))
UNION ALL
SELECT 'Total', SUM(a.LL), SUM(a.EE), ROUND(SUM(a.Total),2)
FROM(
SELECT COALESCE([U C], 'Total') [U C],
SUM(CASE WHEN f_k = 'F' THEN 1 ELSE 0 END) * 100.0 / cnt LL,
SUM(CASE WHEN f_k = 'M' THEN 1 ELSE 0 END) * 100.0 / cnt EE,
COUNT(*) * 100.0 / cnt Total
FROM (SELECT mh2.*, count(*) over () cnt
FROM mh2  
WHERE [U C] IN ('I', 'E', 'T') 
AND f_k IN ('F', 'M')) mh2
GROUP BY GROUPING SETS (([U C], cnt))) a

Upvotes: 0

Views: 46

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

I find conditional aggregation so much simpler:

select coalesce([U C], 'total') as [U C],
       sum( case when fk = 'F' then 1 else 0 end) * 100.0 / cnt as EE,
       sum( case when fk = 'M' then 1 else 0 end) * 100.0 / cnt as LL,
       count(*) * 100.0 / cnt as total
from (select mh2.*, count(*) over () as cnt
      from mh2 
      where [U C] in ('I', 'E', 'T') and
            fk in ('F', 'M')
     ) mh2
group by grouping sets ( ([U C], cnt), (cnt) )

Upvotes: 1

Related Questions