November 27, 2002 at 2:43 pm
I have a stored procedure as shown below -- I am trying to calculate a percent per row for a group of records.
Create Procedure CalculatePromptnessTotals
@startdate varchar(10), @enddate varchar(10)
As
Create table #Responses(Response varchar(50))
Insert #Responses Values('Outstanding')
Insert #Responses Values('Excellent')
Insert #Responses Values('Good')
Insert #Responses Values('Average')
Insert #Responses Values('Poor')
Declare @tot int
Select @tot=Count(*)
From custsatisfaction
Where c.datesubmitted Between @startdate And @enddate
If @tot>0
Select
t.Response, Number = Count(*),
Percentage = count(*)/@tot
From #Responses t
Left Join custsatisfaction c
On t.Response = c.prompt
Where c.datesubmitted Between @startdate And @enddate
Group By t.Response
Drop Table #Responses
My result always comes back with an error How can I total some number over the total number and then multiply it by 100 in SQL Server based on my stored procedure above.
The @tot which represents the number per group (the number of Excellents, of Outstandings) this is represented by the 'Number' variable in my stored procedure. But how do I get a total of all the Responses (Outstanding, Excellent, Good, Average, Poor)? The variable @tot does not appear to count all Response groups together, just an individual group (i.e. the Excellents)
Any help would be appreciated. Thanks
Cheryl
November 27, 2002 at 2:54 pm
Notice you use a left join?
Any value in custsatisfaction that's not contained in #Responses?
First statement counts all custsatisfaction records and second statement consider only records which links with custsatisfaction.
Any NULLS?
November 28, 2002 at 3:21 am
Try
Select t.Response , Count(c.prompt) as 'Number', (Count(c.prompt) * 100)/@tot as 'Percentage'
From #Responses t
Left Join custsatisfaction c
On c.prompt = t.Response
and c.datesubmitted Between @startdate And @enddate
Group By t.Response
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply