sql server programming:
Have you considered:
SELECT Site, calltype, avg(totalscore) as [Avg Score]
FROM DB
WHERE calltype = 'A'
GROUP BY Site, calltype
Your calculation using CASE is averaging zeroes for the rows that do
not meet the WHEN test, which isn't going to work for AVG. Rather
than use ELSE 0, use ELSE NULL or leave off the ELSE entirely and
allow it to default to NULL. With that approach, and hardcoding in
all the values of CallType you mentioned:
SELECT Site,
AVG(CASE WHEN TotalScore > 0 AND CallType = 'A'
THEN Totalscore
END) AS [Avg Score For A],
AVG(CASE WHEN TotalScore > 0 AND CallType = 'B'
THEN Totalscore
END) AS [Avg Score For B],
AVG(CASE WHEN TotalScore > 0 AND CallType = 'C'
THEN Totalscore
END) AS [Avg Score For C],
AVG(CASE WHEN TotalScore > 0 AND CallType = 'D'
THEN Totalscore
END) AS [Avg Score For D]
FROM DB
GROUP BY Site
This might be simplified by moving the test TotalScore > 0 to the
WHERE clause.
Roy Harvey
Beacon Falls, CT
On Mon, 20 Aug 2007 20:04:32 GMT, "Chamark via SQLMonster.com"
[quoted text, click to view] <u21870@uwe> wrote:
>I am trying to get avg score by site, by call type. Columns are Site(varchar),
>Calltype(varchar), totalscore(float). Calltypes are A, B, C, D. Sites are 1,
>2, 3, 4. I can do a straight average statement I can only get one calltype.
>I want to do a CASE statement to get all average scores for all calltypes.
>
>Select Site, avg(totalscore) as [Avg Score]
>FROM DB
>WHERE calltype = 'A'
>GROUP BY Site
>
>Results
>
>Site Avg Score (for A)
>1 85
>2 75.5
>3 85.33
>
>SELECT Site, AVG(CASE WHEN TotalScore > 0 AND CallType = 'A' THEN Totalscore
>ELSE 0 END) AS [Avg Score For A]
>FROM DB
>GROUP BY Site
>
>Results
>
>Site Avg Score For A
>1 i get 8.5
>2 i get 37.75
>3 i get 36.57
>Why am I getting a difference?
I am trying to get avg score by site, by call type. Columns are Site(varchar),
Calltype(varchar), totalscore(float). Calltypes are A, B, C, D. Sites are 1,
2, 3, 4. I can do a straight average statement I can only get one calltype.
I want to do a CASE statement to get all average scores for all calltypes.
Select Site, avg(totalscore) as [Avg Score]
FROM DB
WHERE calltype = 'A'
GROUP BY Site
Results
Site Avg Score (for A)
1 85
2 75.5
3 85.33
SELECT Site, AVG(CASE WHEN TotalScore > 0 AND CallType = 'A' THEN Totalscore
ELSE 0 END) AS [Avg Score For A]
FROM DB
GROUP BY Site
Results
Site Avg Score For A
1 i get 8.5
2 i get 37.75
3 i get 36.57
Why am I getting a difference?
Any help is greatly appreciated - thank you
--
Message posted via
http://www.sqlmonster.com
Hey Roy,
Thanks so much for your time and instruction. Obviously it worked like a
charm. You're the BEST!!!
[quoted text, click to view] Roy Harvey wrote:
>Have you considered:
>
>SELECT Site, calltype, avg(totalscore) as [Avg Score]
>FROM DB
>WHERE calltype = 'A'
>GROUP BY Site, calltype
>
>Your calculation using CASE is averaging zeroes for the rows that do
>not meet the WHEN test, which isn't going to work for AVG. Rather
>than use ELSE 0, use ELSE NULL or leave off the ELSE entirely and
>allow it to default to NULL. With that approach, and hardcoding in
>all the values of CallType you mentioned:
>
>SELECT Site,
> AVG(CASE WHEN TotalScore > 0 AND CallType = 'A'
> THEN Totalscore
> END) AS [Avg Score For A],
> AVG(CASE WHEN TotalScore > 0 AND CallType = 'B'
> THEN Totalscore
> END) AS [Avg Score For B],
> AVG(CASE WHEN TotalScore > 0 AND CallType = 'C'
> THEN Totalscore
> END) AS [Avg Score For C],
> AVG(CASE WHEN TotalScore > 0 AND CallType = 'D'
> THEN Totalscore
> END) AS [Avg Score For D]
>FROM DB
>GROUP BY Site
>
>This might be simplified by moving the test TotalScore > 0 to the
>WHERE clause.
>
>Roy Harvey
>Beacon Falls, CT
>
>>I am trying to get avg score by site, by call type. Columns are Site(varchar),
>>Calltype(varchar), totalscore(float). Calltypes are A, B, C, D. Sites are 1,
>[quoted text clipped - 26 lines]
>>Why am I getting a difference?
>>Any help is greatly appreciated - thank you
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/200708/1
