SQL Calculates Percentage - 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

SQL Server 2005 Forums

Transact-SQL (2005)

SQL Calculates Percentage

Author

Topic

a2l2en
Starting Member

11 Posts

Posted - 2009-01-12 : 00:54:12

I'd like to use SQL to calculate the percentage of helpdesk tickets that are password related for each help desk location (there are 126 of them). The sql below works; however, I can only get it to calculate correctly for a single location. Is there a better way to do this? Maybe a sql pivot or something???

Here is what I want the output to look like:

HelpDeskLocID Password_Issue%
-------------- ------------
Site_1 16.66
Site_2 32.53
Site_3 45.11

SELECT a.HelpDeskLocID, a.complaint_cause password_issue%,
ROUND(COUNT(a.complaint_cause)/b.total_rows *100 ,2)
FROM complaints a,
(SELECT COUNT(ROWID)total_rows
FROM complaints
WHERE
a.HelpDeskLocID = 'Site_1'
and date_entered BETWEEN '01-October-08'
AND '15-October-08') b
WHERE
a.HelpDeskLocID = 'Site_1'
and a.date_entered BETWEEN '01-October-08'
AND '15-October-08'
and a.complaint_cause = 'Password Issue'
GROUP BY a.HelpDeskLocID, a.complaint_cause, b.total_rows;

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2009-01-12 : 01:49:45

try this

SELECT	a.HelpDeskLocID, a.complaint_cause password_issue%,
		ROUND(COUNT(a.complaint_cause)/COUNT(b.HelpDeskLocID) * 100 ,2)
FROM complaints a
INNER JOIN ( SELECT HelpDeskLocID, date_entered FROM complaints) B
		ON B.HelpDeskLocID = A.HelpDeskLocID
		AND B.date_entered = A.date_entered
WHERE	
--		a.HelpDeskLocID = 'Site_1' AND
		a.date_entered BETWEEN '01-October-08' AND '15-October-08' and 
		a.complaint_cause = 'Password Issue'
GROUP BY a.HelpDeskLocID, a.complaint_cause

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-12 : 02:19:22

if this still doesnt give you what you want post some sample data to illustrate what you want

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-12 : 03:30:56

Something like this?

SELECT	HelpDeskLocID,
	1.0E * COUNT(*) OVER (PARTITION BY HelpDeskLocID) / COUNT(*) OVER ()
FROM	Complaints

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources