SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 help AVG
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mike13
Posting Yak Master

Netherlands
214 Posts

Posted - 08/18/2013 :  13:52:47  Show Profile  Click to see mike13's MSN Messenger address  Reply with Quote
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

Sweden
30249 Posts

Posted - 08/18/2013 :  14:05:47  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30249 Posts

Posted - 08/18/2013 :  14:08:41  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30249 Posts

Posted - 08/18/2013 :  14:09:47  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

mike13
Posting Yak Master

Netherlands
214 Posts

Posted - 08/18/2013 :  17:35:16  Show Profile  Click to see mike13's MSN Messenger address  Reply with Quote
tnx
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30249 Posts

Posted - 08/19/2013 :  01:35:13  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

ShivaKrishna
Starting Member

India
20 Posts

Posted - 08/28/2013 :  06:42:14  Show Profile  Reply with Quote
SELECT ProductID, AVG(Rating) AS rating_average
FROM dbo.T_Review
WHERE Rating > 0
GROUP BY ProductID;
Go to Top of Page

kameswararao polireddy
Starting Member

India
19 Posts

Posted - 08/28/2013 :  10:06:26  Show Profile  Reply with Quote
SELECT
ProductID, AVG(Rating) AS rating_average
FROM
dbo.T_Review
WHERE Rating <> 0.0
GROUP BY ProductID

P.Kameswara rao
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000