specific group average
-
I have this stored procedure here. I want to get the course average by course,cost center. as you can see the score as character in the table. I need to take off the Waived and 0% percent. The result did not work out right.
I think I need to GROUP BY [?].[Quiz Name] after the first select statement. but I do not know how to do that. Thx.
ALTER PROCEDURE dbo.[AverageDeptScore] AS
SELECT distinct AVG(CAST(SUBSTRING(A.Score, 1, 2) AS int)) AS AvgScore, HREMP.CC, A.[Quiz Name]
FROM I need to add more here (select * from [Quiz Log] where ([Quiz Log].Score <> 'Waived') AND ([Quiz Log].Score <> '0%')) as A INNER JOIN
HREMP ON [A].[Employee Name] = HREMP.[FULL NAME] AND [A].DOB = HREMP.DOB
GROUP BY [A].[Quiz Name], HREMP.CC, HREMP.CC
HAVING (HREMP.CC = N'010018200')
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-
What result did you expect to get and what result did you actually get? What was wrong with your results?
Russel Loski, MCSE Business Intelligence, Data Platform
-
When I run this query
select distinct [Quiz Name], Score from [Quiz Log] INNER JOIN
HREMP ON [Quiz Log].[Employee Name] = HREMP.[FULL NAME] AND [Quiz Log].DOB = HREMP.DOB
where (HREMP.CC = N'010018200') and ([Quiz Log].Score <> 'Waived' AND [Quiz Log].Score <> '0%')
I got this result. I expected.
Back Injury Prevention average will be 90%.
Back Injury Prevention 100%
Back Injury Prevention 80%
Back Injury Prevention 90%
Corporate Compliance 100%
Corporate Compliance 80%
Corporate Compliance 90%
Ethics 100%
Ethics 80%
Ethics 90%
Event Reporting 100%
Event Reporting 80%
Event Reporting 90%
HIPAA Privacy 100%
HIPAA Privacy 90%
HIPAA Security 100%
HIPAA Security 70%
HIPAA Security 80%
Infection Control 100%
Infection Control 90%
Mission Integration 100%
Mission Integration 90%
Quality Improvement 100%
Quality Improvement 70%
Quality Improvement 80%
Quality Improvement 90%
Safety 100%
Safety 90%
Sharps Injury Prevention 90%
Slips and Falls 100%
Slips and Falls 70%
Slips and Falls 90%
Use this
SELECT distinct AVG(CAST(SUBSTRING(A.Score, 1, 2) AS int)) AS AvgScore, HREMP.CC, A.[Quiz Name]
FROM (select * from [Quiz Log] where ([Quiz Log].Score <> 'Waived') AND ([Quiz Log].Score <> '0%')) as A INNER JOIN
HREMP ON [A].[Employee Name] = HREMP.[FULL NAME] AND [A].DOB = HREMP.DOB
GROUP BY [A].[Quiz Name], HREMP.CC, HREMP.CC
HAVING (HREMP.CC = N'010018200')
I got the result.
56% 010018200 Back Injury Prevention
I think because the average where or group did not come up right here.
Thx.
-
The first thing to try is
AVG(CAST(SUBSTRING(A.Score, 1, len(A.Score) - 1) AS int))
By using SUBSTRING(A.Score, 1, 2) "100%" will be 10.
Russel Loski, MCSE Business Intelligence, Data Platform
-
After I changed the result became:
SELECT distinct AVG(CAST(SUBSTRING(A.Score, 1, len(A.Score) - 1) AS int)) AS AvgScore, HREMP.CC, A.[Quiz Name]
FROM (select * from [Quiz Log] where ([Quiz Log].Score <> 'Waived') AND ([Quiz Log].Score <> '0%')) as A INNER JOIN
HREMP ON [A].[Employee Name] = HREMP.[FULL NAME] AND [A].DOB = HREMP.DOB
GROUP BY [A].[Quiz Name], HREMP.CC
HAVING (HREMP.CC = N'010018200')
92 010018200 Back Injury Prevention
95 010018200 Corporate Compliance
94 010018200 Ethics
92 010018200 Event Reporting
97 010018200 HIPAA Privacy
94 010018200 HIPAA Security
98 010018200 Infection Control
96 010018200 Mission Integration
90 010018200 Quality Improvement
96 010018200 Safety
90 010018200 Sharps Injury Prevention
91 010018200 Slips and Falls
select distinct [Quiz Name], Score from [Quiz Log] INNER JOIN
HREMP ON [Quiz Log].[Employee Name] = HREMP.[FULL NAME] AND [Quiz Log].DOB = HREMP.DOB
where (HREMP.CC = N'010018200') and ([Quiz Log].Score <> 'Waived' AND [Quiz Log].Score <> '0%')
return
Back Injury Prevention 100%
Back Injury Prevention 80%
Back Injury Prevention 90%
Corporate Compliance 100%
Corporate Compliance 80%
Corporate Compliance 90%
Ethics 100%
Ethics 80%
Ethics 90%
Event Reporting 100%
Event Reporting 80%
Event Reporting 90%
HIPAA Privacy 100%
HIPAA Privacy 90%
HIPAA Security 100%
HIPAA Security 70%
HIPAA Security 80%
Infection Control 100%
Infection Control 90%
Mission Integration 100%
Mission Integration 90%
Quality Improvement 100%
Quality Improvement 70%
Quality Improvement 80%
Quality Improvement 90%
Safety 100%
Safety 90%
Sharps Injury Prevention 90%
Slips and Falls 100%
Slips and Falls 70%
Slips and Falls 90%
Which is not right , too? Thx.
The first three answers should be 90%. The first two is bigger. the third one should be smaller.
-
Do you allow nulls to any of the agregate columns?
-
NULLABLE is allowable. but all the data would be on the column.
-
--I don't know if you are experiencing that particular problem but it's always worth mentioning it
SELECT
AVG(dtDemo.N) AS Avg1
, COUNT(*) AS Count1
, SUM(dtDemo.N) AS Sum1
, SUM(dtDemo.N) /COUNT(*) AS AVG2
FROM (
SELECT 4 AS N
UNION ALL
SELECT NULL
UNION ALL
SELECT 4
UNION ALL
SELECT 4
 
dtDemo -
.hard to comprehend you demo
-
What are you not understanding?
Do you see something wrong with the results of the 2 averages?
-
I do not understand your union statement.
92 010018200 Back Injury Prevention 5 460
95 010018200 Corporate Compliance 8 760
94 010018200 Ethics 5 470
92 010018200 Event Reporting 5 460
97 010018200 HIPAA Privacy 7 680
94 010018200 HIPAA Security 9 850
98 010018200 Infection Control 6 590
96 010018200 Mission Integration 6 580
90 010018200 Quality Improvement 8 720
96 010018200 Safety 6 580
90 010018200 Sharps Injury Prevention 2 180
91 010018200 Slips and Falls 6 550
92 010018200 Back Injury Prevention 5 460
95 010018200 Corporate Compliance 8 760
94 010018200 Ethics 5 470
92 010018200 Event Reporting 5 460
97 010018200 HIPAA Privacy 7 680
94 010018200 HIPAA Security 9 850
98 010018200 Infection Control 6 590
96 010018200 Mission Integration 6 580
90 010018200 Quality Improvement 8 720
96 010018200 Safety 6 580
90 010018200 Sharps Injury Prevention 2 180
91 010018200 Slips and Falls 6 550
if I do this way
SELECT AVG(CAST(SUBSTRING(A.Score, 1, len(A.Score) - 1) AS int)) AS AvgScore, HREMP.CC, A.[Quiz Name], Count(*) as CountT, Sum(CAST(SUBSTRING(A.Score, 1, len(A.Score) - 1) AS int)) as SumT, SumT/CountT
FROM (select * from [Quiz Log] where ([Quiz Log].Score <> 'Waived') AND ([Quiz Log].Score <> '0%')) as A INNER JOIN
HREMP ON [A].[Employee Name] = HREMP.[FULL NAME] AND [A].DOB = HREMP.DOB
GROUP BY [A].[Quiz Name], HREMP.CC
HAVING (HREMP.CC = N'010018200')
It will give me the error
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'SumT'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'CountT'.
-
I was just demonstrating with a sample dataset that AVG and sum/count() handle nulls differently. Now to think of it I would assume that the avg would be too low and not too high so I guess you have a different problem on your hand. Have you checked that the where conditions return the correct data and that there's no invalid data in the tables?
-
Yes. I check all the data no null data in the column and I check the where . it is OK.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply