Percentage in Case statement

Question

Post reply

Percentage in Case statement

moorthy.jy

SSCommitted

Points: 1753
More actions
November 27, 2009 at 3:08 am

#98419

Hi,
I have a table like this:CREATE TABLE #TEMP (
Col1 int not null,
Type int not null
)
GO
INSERT #TEMP
SELECT 1,1
UNION
SELECT 2,1
UNION
SELECT 3,2
UNION
SELECT 4,2
UNION
SELECT 5,2
I want the result to show like this
Total TYPE 1 PERC1 TYPE 2 PERC2
----------- ----------- ----- ----------- -----
5 2 40.00 3 60.00
This is the code i have,
SELECT COUNT(*) 'Total',
sum(case WHEN TYPE = 1 THEN 1 ELSE 0 END) AS 'TYPE 1',
0.00 AS 'PERC1',
sum(case WHEN TYPE = 2 THEN 1 ELSE 0 END) AS 'TYPE 2',
0.00 AS 'PERC2'
FROM #TEMP
Please help me in this SQL.
Thanks in advance

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Something along those lines should work:

SELECT COUNT(*) 'Total',

sum(case WHEN TYPE = 1 THEN 1 ELSE 0 END) AS 'TYPE 1',

sum(case WHEN TYPE = 1 THEN 1.0 ELSE 0 END) /Count(*) *100 AS 'PERC1',

sum(case WHEN TYPE = 2 THEN 1 ELSE 0 END) AS 'TYPE 2',

sum(case WHEN TYPE = 2 THEN 1.0 ELSE 0 END) /Count(*) *100 AS 'PERC2'

FROM #TEMP

/*result set

TotalTYPE 1PERC1TYPE 2PERC2

5240.000000360.000000

*/

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 2

Try This.....

CREATE TABLE #TEMP (

Col1 int not null,

Type int not null

)

GO

INSERT #TEMP

SELECT 1,1

UNION

SELECT 2,1

UNION

SELECT 3,2

UNION

SELECT 4,2

UNION

SELECT 5,2

DECLARE @TotCnt INT

Select @TotCnt=COUNT(*) from #TEMP

Select Count(*) Type,CONVERT(DECIMAL(20,2),Count(*)/5.0*100) as count INTO #temp1 from #TEMP

GROUP BY Type

DECLARE @sql varchar(8000)

DECLARE @coalesce Varchar(100)

SELECT @coalesce =COALESCE(@COALESCE +',','')+ convert(varchar,Type)+','+convert(varchar,Count) FROM #temp1

SET @coalesce =Isnull(@COALESCE ,0)

SELECT convert(varchar,@TotCnt)+','+@COALESCE

DROP TABLE #TEMP1

DROP TABLE #TEMP

Thanks
Parthi

moorthy.jy SSCommitted Points: 1753 More actions · Answer 3

Thanks all. Lutz, this is what i want. thanks. Parthi, i think, the solution is bit complicated for a simple select. thanks .

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 4

Hi Moorthy

I just done this for dynamic one.

If u know how many types you have then no problem you can use case but if you dont know the types i think this might be useful thats why i did so, any how if you get the output then is Nice

CREATE TABLE #TEMP (

Col1 int not null,

Type int not null

)

GO

INSERT #TEMP

SELECT 1,1

UNION SELECT 2,1

UNION SELECT 3,2

UNION SELECT 4,2

UNION SELECT 5,2

UNION SELECT 6,3

UNION SELECT 7,3

UNION SELECT 8,3

UNION SELECT 9,3

DECLARE @TotCnt INT

Select @TotCnt=COUNT(*) from #TEMP

Select Count(*) Type,CONVERT(DECIMAL(20,2),Count(*)/CONVERT(Decimal(20,2),@TotCnt)*100) as count INTO #temp1 from #TEMP

GROUP BY Type

DECLARE @sql varchar(8000)

DECLARE @coalesce Varchar(100)

SELECT @coalesce =COALESCE(@COALESCE +',','')+ convert(varchar,Type)+','+convert(varchar,Count) FROM #temp1

SET @coalesce =Isnull(@COALESCE ,0)

SELECT convert(varchar,@TotCnt)+','+@COALESCE

DROP TABLE #TEMP1

DROP TABLE #TEMP

Thanks

Parthi

Thanks
Parthi

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

Isn't this the best solution for the stated problem?

select Total, Type1Count, Type1Count*100.0/Total as Perc1, Type2Count, Type2Count*100.0/Total as Perc2

FROM (

select count(*) as Total,

sum(case when type = 1 then 1 else 0 end) as Type1Count,

sum(case when type = 2 then 1 else 0 end) as Type2Count

from #temp) as t

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

LutzM SSC Guru Points: 107049 More actions · Answer 6

TheSQLGuru (12/1/2009)
Isn't this the best solution for the stated problem?
select Total, Type1Count, Type1Count*100.0/Total as Perc1, Type2Count, Type2Count*100.0/Total as Perc2
FROM (
select count(*) as Total,
sum(case when type = 1 then 1 else 0 end) as Type1Count,
sum(case when type = 2 then 1 else 0 end) as Type2Count
from #temp) as t

It seems like both solutions (yours and mine a few posts up) will use exactly the same execution plan.

So, it becomes an issue of readability.

Maybe a CTE will be even more readable (still using the same execution plan). But that's more an issue of what type of general programming style someone is used to, I think...

;WITH t as

(

select count(*) as Total,

sum(case when type = 1 then 1 else 0 end) as Type1Count,

sum(case when type = 2 then 1 else 0 end) as Type2Count

from #temp

)

select Total, Type1Count, Type1Count*100.0/Total as Perc1, Type2Count, Type2Count*100.0/Total as Perc2

FROM t

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

Actually LMU our queries do not produce the same plan. It only seems that way due to the limitations of the graphical query plan you probably looked at. Put your pointer over the icons and you will see that your query computes 12 scalars and does 5 stream aggregates. My query computes 9 scalars and 3 stream aggregates. Certainly not a big deal, especially with just 5 rows, but mine 'should' be more efficient ... 😉

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service