In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.
Become a Premium Member and unlock a new, free course in leading technologies each month.
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
declare @tab table(Risk int, Amount numeric(10,2))
insert into @tab
Select 1, NULL
union select 2, 0.00
union select 3, 15282.95
union select 4, 33159.88
union select 5, 0.00
union select 6, 0.00
;
SELECT 32111 SPPARAM,[1], [2], [3], [4], [5],[6]
FROM
(SELECT Risk, isnull(Amount,0)/ Total * 100.0 TOT
FROM @tab, (Select Sum(Amount) Total from @tab) A) AS SourceTable
PIVOT
(
sum(TOT)
FOR RISK IN ([1], [2], [3], [4], [5],[6])
) AS PivotTable
declare @tab table(Risk int, Amount numeric(10,2))
insert into @tab
Select 1, NULL
union select 2, 0.00
union select 3, 15282.95
union select 4, 33159.88
union select 5, 0.00
union select 6, 0.00
;
SELECT SPPARAM,
sum(case when Risk=1 then AMOUNT else 0 end )/sum(Amount) * 100.0 [1],
sum(case when Risk=2 then AMOUNT else 0 end )/sum(Amount) * 100.0 [2],
sum(case when Risk=3 then AMOUNT else 0 end )/sum(Amount) * 100.0 [3],
sum(case when Risk=4 then AMOUNT else 0 end )/sum(Amount) * 100.0 [4],
sum(case when Risk=5 then AMOUNT else 0 end )/sum(Amount) * 100.0 [5],
sum(case when Risk=6 then AMOUNT else 0 end )/sum(Amount) * 100.0 [6]
FROM
(SELECT 32111 SPPARAM,Risk, isnull(Amount,0) AMOUNT
FROM @tab) AS SourceTable
Group by SPPARAM
declare @tab table(Risk int, Amount numeric(10,2))
insert into @tab
Select 1, NULL
union select 2, 0.00
union select 3, 0 --15282.95
union select 4, 0 --33159.88
union select 5, 0.00
union select 6, 0.00
;
SELECT SPPARAM,
sum(case when Risk=1 then AMOUNT else 0 end )/ case when sum(Amount)=0 then 1 else 0 end * 100.0 [1],
sum(case when Risk=2 then AMOUNT else 0 end )/case when sum(Amount)=0 then 1 else 0 end * 100.0 [2],
sum(case when Risk=3 then AMOUNT else 0 end )/case when sum(Amount)=0 then 1 else 0 end * 100.0 [3],
sum(case when Risk=4 then AMOUNT else 0 end )/case when sum(Amount)=0 then 1 else 0 end * 100.0 [4],
sum(case when Risk=5 then AMOUNT else 0 end )/case when sum(Amount)=0 then 1 else 0 end * 100.0 [5],
sum(case when Risk=6 then AMOUNT else 0 end )/case when sum(Amount)=0 then 1 else 0 end * 100.0 [6]
FROM
(SELECT 32111 SPPARAM,Risk, isnull(Amount,0) AMOUNT
FROM @tab) AS SourceTable
Group by SPPARAM
If you are experiencing a similar issue, please ask a related question
Join the community of 500,000 technology professionals and ask your questions.