help AVG - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2008 Forums

Transact-SQL (2008)

help AVG

Author

Topic

mike13
Posting Yak Master

219 Posts

Posted - 2013-08-18 : 13:52:47

Hi all,

I got product reviews.
and i used

SELECT TOP (100) PERCENT ProductID, AVG(Rating) AS rating_average
FROM dbo.T_Review
GROUP BY ProductID

But i got a product that has 3 reviews, and both give the max 5 (0-5 scale)
But the avg returns a 3, instead of 5.

Probably not using it in the correct way.

Thanks a lot

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-18 : 14:05:47

You are probably counting the 0's in the average.
Try this

SELECT ProductID, AVG(1E * NULLIF(Rating, 0)) AS rating_average
FROM dbo.T_Review
GROUP BY ProductID

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-18 : 14:08:41

The "1E" part is to avoid the integer division you experienced before.

5 + 5 + 0 = 10
10 / 3 = 3 (integer part).

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-18 : 14:09:47

SELECT ProductID, 1E * SUM(Rating) / SUM(SIGN(Rating)) AS rating_average
FROM dbo.T_Review
GROUP BY ProductID;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

mike13
Posting Yak Master

219 Posts

Posted - 2013-08-18 : 17:35:16

tnx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-19 : 01:35:13

SELECT ProductID, AVG(1E * Rating) AS rating_average
FROM dbo.T_Review
WHERE Rating > 0
GROUP BY ProductID;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

ShivaKrishna
Starting Member

20 Posts

Posted - 2013-08-28 : 06:42:14

SELECT ProductID, AVG(Rating) AS rating_average
FROM dbo.T_Review
WHERE Rating > 0
GROUP BY ProductID;

kameswararao polireddy
Starting Member

19 Posts

Posted - 2013-08-28 : 10:06:26

SELECT
ProductID, AVG(Rating) AS rating_average
FROM
dbo.T_Review
WHERE Rating <> 0.0
GROUP BY ProductID

P.Kameswara rao

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources