SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Calculate Percentage During Query?		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mitin
Yak Posting Veteran

54 Posts
Posted - 01/22/2013 :  08:47:18  Show Profile  Reply with Quote
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 :)
Edited by - mitin on 01/22/2013 08:51:04

James K
Flowing Fount of Yak Knowledge

1511 Posts
Posted - 01/22/2013 :  09:03:21  Show Profile  Reply with Quote
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
Go to Top of Page

mitin
Yak Posting Veteran

54 Posts
Posted - 01/22/2013 :  09:17:06  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

1511 Posts
Posted - 01/22/2013 :  09:32:39  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000