Calculate Percentage During Query? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Calculate Percentage During Query?

Author

Topic

mitin
Yak Posting Veteran

81 Posts

Posted - 2013-01-22 : 08:47:18

Hi,

I have the below query:


select distinct eventid, sum(tally) as numberofevents
from data_table
where eventid in
(
'516',
'517',
'531',
'533',
'535',
'516',
'517'
) and lastoccurrence >= '1012-12-19' and lastoccurrence < '2012-12-20'
group by eventid, tally

which returns data as in the below example:


eventid    numberofevents

1024       15481
1026       2
1027       4
1031       57

I want my query to calculate a grand total for the number of events and display this in another column in the results, and i also wish to calculate a percentage for each eventid, showing its contribution towards the total.

Can anyone help/know how this would be done?

Many thanks :)

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-22 : 09:03:21

Do you mean something like this?

select distinct eventid, sum(tally) as numberofevents,
SUM(SUM(tally)) OVER() AS Totalnumberofevents,
100.0*sum(tally)/SUM(SUM(tally)) OVER() AS Percentage
from data_table
where eventid in
(
'516',
'517',
'531',
'533',
'535',
'516',
'517'
) and lastoccurrence >= '1012-12-19' and lastoccurrence < '2012-12-20'
group by eventid

mitin
Yak Posting Veteran

81 Posts

Posted - 2013-01-22 : 09:17:06

Thanks James :) that seems to do the job.

The percentages aren't shown particularly clearly though, for example

for the eventID 1024 the percentage is '12.923449369730'

how can I round up or down or just get this in a more human readable format...

cheers

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-22 : 09:32:39

You can round it or cast it to decimal with the appropriate scale - for example,

CAST(100.0*sum(tally)/SUM(SUM(tally)) OVER() AS DECIMAL(19,2))  AS Percentage

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources