conting average - 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

General SQL Server Forums

New to SQL Server Programming

conting average

Author

Topic

mukhan85
Starting Member

46 Posts

Posted - 2009-06-23 : 18:11:23

Hi folks,
I have a table that looks like the following:

Name #papers
----- -------
Ann 5
jimmy 3
Ann 7
Ann 4
jimmy 3

So the average for Ann is (5+7+3)/3 = 5 (formula: total#of_papers/#rows that person appeared)
for Jimmy: average = (3+3)/2 = 3.
Could you please suggest efficient query for that?

Thanks.

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-06-23 : 18:26:18

[code]DECLARE @t TABLE ([Name] varchar(20), [#papers] int)
INSERT INTO @t
SELECT 'Ann', 5 UNION ALL
SELECT 'Jimmy', 3 UNION ALL
SELECT 'Ann', 7 UNION ALL
SELECT 'Ann', 4 UNION ALL
SELECT 'Jimmy', 3

SELECT DISTINCT [Name], AVG([#papers])
FROM @t
GROUP BY [Name][/code]

Some days you're the dog, and some days you're the fire hydrant.

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-06-23 : 18:28:25

If you want precision with your averages:

SELECT DISTINCT [Name], AVG([#papers]*1.0) 
FROM @t
GROUP BY [Name]

Some days you're the dog, and some days you're the fire hydrant.

mukhan85
Starting Member

46 Posts

Posted - 2009-06-23 : 18:41:53

Thank you Skorch!

nathans
Aged Yak Warrior

938 Posts

Posted - 2009-06-23 : 18:42:57

What is purpose of DISTINCT in your query?

mukhan85
Starting Member

46 Posts

Posted - 2009-06-23 : 18:43:58

what if I won't use DISTINCT?
it seems the query produces the same result and using DISTINCT just adds more overhead?

nathans
Aged Yak Warrior

938 Posts

Posted - 2009-06-23 : 18:46:17

Exactly my point.

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-06-23 : 19:11:47

You're right. DISTINCT is completely redundant there.

Some days you're the dog, and some days you're the fire hydrant.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources