adding percentages - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

adding percentages

Author

Topic

tariq2
Posting Yak Master

125 Posts

Posted - 2010-09-28 : 07:31:07

The following query provides a list of users by reportcount,
so James Smith 200, Simon Smith 190 etc.etc.
But I also need to see the % as a % of the grandtotal,
what do I need to add to my query?
Many thanks

-- A selects username and reportcount by user
select username,sum(reportcount),

-- B selects total reportount as GrandTotal
(select SUM(reportcount) from View_BIUsers_ReportData where ReportID = 'db_sales' and TreeYear = '2010' )as GrandTotal

from View_BIUsers_ReportData where ReportID = 'db_sales' and TreeYear = '2010' group by UserName order by sum(ReportCount) desc

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-09-28 : 07:51:34

[code]
select username, ReportCount, GrandTotal, (ReportCount/GrandTotal)*100 as ReportPercent
from
(
select username,sum(reportcount) as ReportCount,
(select SUM(reportcount) from View_BIUsers_ReportData where ReportID = 'db_sales' and TreeYear = '2010' )as GrandTotal
from View_BIUsers_ReportData
where ReportID = 'db_sales' and TreeYear = '2010'
group by UserName
) t
order by ReportCount desc[/code]

Harsh Athalye
http://www.letsgeek.net/

tariq2
Posting Yak Master

125 Posts

Posted - 2010-09-28 : 08:24:02

Thats brilliant Harsh!
Can I just ask, what is the role of the 't' after the final bracket ?

Tahnk you

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-09-28 : 09:00:17

It's an alias given to the derived table i.e. the inner query. It can be used to refer to column names from inner query.

Harsh Athalye
http://www.letsgeek.net/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources