Pivoting percentages
-
In Excel, you can display the data in a pivot table as a numeric value or a percentage. I have a user that wants me to do the same thing in SQL. Display 2 tables (same query), but do percentages as one of the displays with true numbers as the other display.
I've finally figured out how to get the true numbers (rounded without decimal points). But I cannot, for the life of me, figure out which functions I can use to display the percentages in T-SQL. The percentages in each column have to total up to be 100%.
Results would be similar to:
MoLag HC HD
6 15% 32%
2 35% 61%
1 50% 7%
Total 100% 100%
Current Code is:
Select MoLag, [HC], [HD], [HF], [HI], [HJ], [HK],
, [HW]from (Select MoLag, causeCd, Convert(int,Round(isnull(benAmt,0),0,0)) as benAmt from Paid) as BenAmtPerMoLag
PIVOT
( Sum(benAmt) FOR causeCd IN ([HC], [HD], [HF], [HI], [HJ], [HK],
, [HW]) ) as MyPivotOrder by MoLag DESC;
The above code generates true numbers, rounded to the nearest dollar, without decimal points and which substitutes 0 for NULL values so that any addition in the PIVOT doesn't end up as NULL by accident. Unfortunately, if there are no numbers to add at all, it still ends up show NULLS in the final PIVOT resultset. I'm still trying to figure that one last piece out.
But my most important issue is the percentages. Any thoughts on how I can change the above code to show percentages per column?
-
First thought would be: don't use the new PIVOT notation for that - go back to the "old" SQL2000 notation, using the CASE statement instead.
something like:
select molag,
sum(case when causecd='hc' then benamt else 0 end)/HCTOTAL hcperc,
sum(case when causecd='hd' then benamt else 0 end)/HdTOTAL hdperc,
sum(case when causecd='hf' then benamt else 0 end)/HfTOTAL hfperc
from
paid cross join
(select sum(case when causecd='hc' then benamt else 0 end) HCTotal,
sum(case when causecd='hd' then benamt else 0 end) HdTotal,
sum(case when causecd='hf' then benamt else 0 end) HfTotal
from Paid) CauseTots
GROUP BY molag
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
Except that still doesn't give me percentages. I can't find a function that does.
Any thoughts?
-
I missed the min statement last time - but look closer - this does return percentages of total. The only thing it doesn't do is make the number coming out "look" pretty (so - instead of 15%, it's 0.15).
The HCTotal, HFTotal and HDTotal are grouped total by causeCD (meaning the output columns each add up to 100%).
This code "works" for me (assuming I'm understanding what you're up against):
select
molag,
sum
(case when causecd='hc' then benamt else 0 end)/min(HCTOTAL) hcperc,
sum
(case when causecd='hd' then benamt else 0 end)/min(HdTOTAL) hdperc,
sum
(case when causecd='hf' then benamt else 0 end)/min(HfTOTAL) hfperc
from
paid --initial table
cross join
(
select sum(case when causecd='hc' then benamt else 0 end) HCTotal,
sum
(case when causecd='hd' then benamt else 0 end) HdTotal,
sum
(case when causecd='hf' then benamt else 0 end) HfTotal
from
Paid) CauseTots --Totals by CauseCD
GROUP
BY molag
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
Hmmm. I'm going to have to play with it because I keep getting "Divide by Zero" errors with your code. Probably due to the ELSE 0 statement in the Cross Joined select query.
And when I substitute 1 or 100, the numbers are waaaayy off.
Thanks, Matt.
-
Oh, the drama! @sigh.
Well, I've figured out how to get around the Divide by Zero error, but I can only make it work for one column. I've also finally figured out how to get my percentages with the % in the number, but DBZ error rears its ugly head unless I only do one column's worth of data.
To top it off, I have to make the whole thing dynamic so when I add more CauseCD codes, I don't have to keep opening up the query and reworking it every time.
For those of you who are reading this thread in the future to find a solution, here's the method I used to get my percentages the way they show up in Excel:
Convert(varchar(5),Convert(int, Convert(decimal(5,2),
Round(Sum(CASE When CauseCD = 'HC' then benAmt Else 0 End)/min(HCTot), 2, 0)) * 100)) + '%' as HC
I used ROUND() to get the extra decimals to bump up to the next "percentage"), then converted to decimal with 2 places on the right of the point, then multiplied by 100 to put the percent on the left side of the decimal, then converted to Int to get rid of the decimal and the precision, lastly, I converted to a varchar and added the % sign to the end of it all.
To get rid of the "Divide By Zero", I added a WHERE clause above the GROUP BY (Where HCTot <> 0) because I have yet to figure out how I can get my division done properly up in the SELECT/CASE section. Any thoughts on that would be greatly appreciated.
Total Code (currently) is below. As you can see, it's the WHERE clause that's killing my ability to add other columns, so I have to find the right value to put in the ELSE part of the CASE.
Select Distinct MoLag,
Convert(varchar(5),Convert(int, Convert(decimal(5,2),
Round(Sum(CASE When CauseCD = 'HC' then benAmt Else 0 End)/min(HCTot), 2, 0)) * 100)) + '%'
HC
from PAID
Cross JOIN (Select Sum(Case When causeCd = 'HC' then benAmt Else 0 End) HCTot from PAID
Group By CauseCd ) CauseTots
where HCTot <> 0
Group by MoLag
Order by MoLag;
-
Sounds to me like we got some categories with no data, or 0 in the benamt for all records.
Try putting this instead of the min(HcTot) -
Min(case when HCtot=0 then 1 end)
I'd be curious to see what you get when you split the numerator and denominator into separate fields (for grins and giggles).
By the way - you seem to have declared an unused @sigh at the top of that last post
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
HA! Yes, I have declared an unused @sigh. I'll see if I can't fix that and use it... over and over and over.... @=)
I tried your suggestion:
Msg 8115, Level 16, State 8, Line 2
Arithmetic overflow error converting numeric to data type numeric.
SQL Server just doesn't like me this morning.
BTW, substituting a 1 at the top Select is essentially as substituting a 1 in ELSE part of the Cross Joined Select, is it not? And I've already determined that setting it as 1 down there doesn't give me the correct percentages.
-
not the same. If you throw the one in up top - you're essentially saying - if the TOTAL for cause HC is 0 then don't divide by 0. if you throw it into the bottom - you'd be throwing in 1's for each HC record with a 0 benamt. So for the following set of HC records
HC 0
hc 0
hc 0
hc 100
in one case hctot=100, and the other Hctot=103.
By the way - try this edit on for size (you snuck an extra group by into the cross join query). Remove what's in red - it's "killing" the cross join. If done right - the cross join subquery query should return one record only with the total in it (or each causecd total when you expand it).
from PAID
Cross JOIN (Select Sum(Case When causeCd = 'HC' then benAmt Else 0 End) HCTot from PAID
Group By CauseCd) CauseTotsBy throwing in the GROUP BY - you're introducing your zeroes, when you hit all of the OTHER groups (HD, HF, etc...)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
DUH! You're absolutely correct. The extra GROUP BY was causing my DBZ error...
Now, to see if I can make this puppy dynamic...
-
WHOOHOO!
I had to sacrifice my % sign because the table I was updating had integers in the columns instead of characters, but I finally made my percentages Pivot table dynamic. A bit of a pain, though... Here's the code for anyone who's interested. And if you see a shorter (easier) way to do this, please let me know.
If (Select name from sys.objects where name = 'TempPropPivot') is NOT NULL
Drop table dbo.TempPropPivot;
If (Select Object_ID('tempdb..#TempColNames')) IS NOT NULL
Drop Table #TempColNames;
If (Select Object_ID('tempdb..#myTempPropPivot')) IS NOT NULL
Drop Table #myTempPropPivot;
GO
Declare @cols varchar(4000);
Select @cols = COALESCE(@cols + ', ' + UPPER(CauseCd), UPPER(CauseCd) ) from vCauseCdDates
Where CauseCd is NOT NULL and CauseCd <> ' ' Order by CauseCdDate;
--Select @cols; --For checking values when troubleshooting
Declare @PivotSQL varchar(8000);
set @PivotSQL = 'Select MoLag, ' + @cols
+ ' into TempPropPivot from (Select MoLag, causeCd, Convert(int,Round(isnull(benAmt,0),0,0)) as benAmt
from PAID) as BenAmtPerMoLag
PIVOT
( Sum(benAmt) FOR causeCd IN ( ' + @cols + ' ) ) as PropPivot Order by MoLag;'
--Select @PivotSQL; --For checking values when troubleshooting
Exec (@PivotSQL);
GO
Select * into #myTempPropPivot from TempPropPivot; --Keeps old values so I can doublecheck them against new values
Select Distinct so.Name as TblName, sc.Name as ColName
into #TempColNames
from sys.objects so
join sys.columns sc
on so.Object_ID = sc.Object_ID
where so.Type = 'U'
and so.Name = 'TempPropPivot';
--Get all Pivoted column names
GO
Alter Table #TempColNames
Add DoneNotDone bit Default 0;
--For making sure I don't repeat work on the same column name
GO
Update #TempColNames
Set DoneNotDone = 1
Where ColName = 'MoLag';
--This is the stable column. Don't want to update this at all.
GO
--Below is Loop which fixes & updates everything
Declare @MyCols int, @CurColName char(2), @PivotUpdate varchar(200), @PerPivotUpdate varchar(4000);
Select @MyCols = Count(*) from #TempColNames;
While @MyCols > 0
Begin
Set @CurColName = (Select Top 1 ColName from #TempColNames where DoneNotDone = 0 or DoneNotDone IS NULL);
--Sets next column Name
Set @PivotUpdate = 'Update TempPropPivot Set ' + @CurColName + ' = 0 Where ' + @CurColName + ' is NULL';
Exec (@PivotUpdate);
--Above fixes all NULLS to be zeros so I don't come up with NULL percentages
Set @PerPivotUpdate = 'Update TempPropPivot Set ' + @CurColName + ' = (Select Convert(int, Convert(decimal(5,2),
Round(Sum(CASE When CauseCD = ''' + @CurColName + ''' then benAmt Else 0 End)/min(Case When ' + @CurColName
+ 'Tot = 0 Then 1 ELSE ' + @CurColName + 'Tot END), 2, 0)) * 100) AS ' + @CurColName
+ ' from PAID hpl
Cross JOIN (Select Sum(Case When causeCd = ''' + @CurColName + ''' then benAmt Else 0 End) AS ' + @CurColName + 'Tot
from PAID ) CauseTots Where hpl.MoLag = TempPropPivot.MoLag Group By hpl.MoLag )'
--Select @PerPivotUpdate --Checks Update code for troubleshooting
Exec (@PerPivotUpdate)
--Above sets all percentages as Integers without the percent sign
Update #TempColNames
Set DoneNotDone = 1
Where ColName = @CurColName;
--Above says I'm done with the column
Set @MyCols = @MyCols -1;
--Above decrements my counter variable for the loop
End
GO
Select * from #TempColNames;
Select * from TempPropPivot;
Select * from #myTempPropPivot
GO
Notice that I used the original Dynamic Pivot to do a SELECT INTO so I could simply change the values in that new table to percentages instead of using the manual CASE statement suggested previously. This way, when I add a new CauseCd to my base table, I don't have to re-write the manual CASE-Wants-To-Be-A-Pivot table.
WHOOHOO! I'm sooooo happy. Now I can go to lunch. @=) And thank you, Matt, for all your help. Without your case statement I never would have been able to get here.
-
Glad to be of help!
Reusing the accumulated values is a great idea. Aggregate operations are a performance drag, so not doing them multiple times over would be encouraged
Just make sure you have NO spaces in the causeCd's , or you'll be getting some odd errors (you'd have to build two different @col's, one with brackets (for the initial select), one without (for the Pivot..In syntax)). but - I'm feeling hopeful I won't ruin your lunch with that one, since that looks to be a varchar(2) or something close...
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
Actually, it's a char(2) and the value will never be less (unless it's NULL and I've got that check taken care of).
But the warning is well heeded. There are days when simple things like that can ruin a person's day. @=)
Thanks again, Matt!
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply