Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 find percentage for rows?

Author  Topic 

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-10-30 : 17:42:32
Hi.
Suppose we have 2 columns.
Values - Percentage
How would we find the percentage of each row?
Meaning, of the total count or values(here total = 100):
Values percentage
50 50%
20 20%
10 10%
20 20%
Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-10-31 : 00:02:50
[code]
SELECT Values, Value * 100.0 / SUM(Values) OVER() as percentage
FROM yourtable
[/code]


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-10-31 : 03:48:02
Hi. I have this query:

--Total Admissions \ Cinema
SELECT Cinema_DisplayName,
Sum(Admissions) AS Admissions,
Sum(CoolAdmissions) AS CoolAdmissions
-- Admissions * 100.0 / Sum(Admissions) over () as percentage
FROM ZZ_vwpProgressBoard
GROUP BY Cinema_DisplayName
ORDER BY MAX(Cinema_Order)


however if it try it like below it will expand the admissions and cooladmissions rows. something that i want to avoid.
 
--Total Admissions \ Cinema
SELECT Cinema_DisplayName,
Sum(Admissions) AS Admissions,
Sum(CoolAdmissions) AS CoolAdmissions,
Sum(Admissions+CoolAdmissions) * 100.0 / Sum(Admissions) over () as percentage
FROM ZZ_vwpProgressBoard
GROUP BY Cinema_DisplayName,Admissions,CoolAdmissions
ORDER BY MAX(Cinema_Order)

Go to Top of Page

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-10-31 : 03:49:27
quote:
Originally posted by sapator

Hi. I have this query:

--Total Admissions \ Cinema
SELECT Cinema_DisplayName,
Sum(Admissions) AS Admissions,
Sum(CoolAdmissions) AS CoolAdmissions
-- Admissions * 100.0 / Sum(Admissions) over () as percentage
FROM ZZ_vwpProgressBoard
GROUP BY Cinema_DisplayName
ORDER BY MAX(Cinema_Order)


however if it try it like below it will expand the admissions and cooladmissions rows. something that i want to avoid.
 
--Total Admissions \ Cinema
SELECT Cinema_DisplayName,
Sum(Admissions) AS Admissions,
Sum(CoolAdmissions) AS CoolAdmissions,
Sum(Admissions+CoolAdmissions) * 100.0 / Sum(Admissions+CoolAdmissions) over () as percentage
FROM ZZ_vwpProgressBoard
GROUP BY Cinema_DisplayName,Admissions,CoolAdmissions
ORDER BY MAX(Cinema_Order)



Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-10-31 : 20:39:08
[code]
--Total Admissions \ Cinema
SELECT Cinema_DisplayName,
Sum(Admissions) AS Admissions,
Sum(CoolAdmissions) AS CoolAdmissions,
Sum(Admissions+CoolAdmissions) * 100.0 / SUM(Sum(Admissions+CoolAdmissions)) over () as percentage
FROM ZZ_vwpProgressBoard
GROUP BY Cinema_DisplayName,Admissions,CoolAdmissions
ORDER BY MAX(Cinema_Order)
[/code]


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

arun_kumar112
Starting Member

2 Posts

Posted - 2014-11-01 : 11:29:11
select (ROW_NUMBER() over(order by id asc)*100.0)/(select count(*) from test),* from test

Arun Kumar
Go to Top of Page

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-11-02 : 13:42:00
Ok thanks guys.
Go to Top of Page
   

- Advertisement -