Clever Geek Handbook

Force AVG to return 0 instead of NULL when preformatting to an empty set in SQL Server 2008

I have two tables called Reviews and Levels.

CREATE TABLE [dbo].[Reviews](
    [ReviewID] [int] IDENTITY(1,1) NOT NULL,
    [Rating] [float] NOT NULL,
    [LevelID] [int] NOT NULL,

CREATE TABLE [dbo].[Levels](
    [Name] [varchar](50) NOT NULL,
    [Blocks] [char](960) NOT NULL,
    [LevelID] [int] IDENTITY(1,1) NOT NULL,

I want to be able to select a main Levels table with an additional column that will be the average of all reviews associated with that LevelID. I have this code

SELECT   AVG(Rating) AS Average, Name,Levels.LevelID,Blocks
FROM     Reviews, Levels
WHERE    Levels.LevelID = Reviews.LevelID
GROUP BY Levels.LevelID, Name, Blocks

This returns correct results for levels that have at least one overview. I want to be able to return levels that have no review and also give an average rating of 0.

+3


source to share


2 answers


Try with this query ... it will definitely solve your problem ....



SELECT ISNULL(AVG(Rating),0) AS Average, Name,Levels.LevelID,Blocks
FROM Levels LEFT JOIN Reviews
ON Levels.LevelID = Reviews.LevelID
GROUP BY Levels.LevelID, Name, Blocks
+5


source 


SELECT   AVG(Rating) AS Average, Name, Levels.LevelID, Blocks
FROM     Reviews, Levels
WHERE    Levels.LevelID = Reviews.LevelID
GROUP BY Levels.LevelID, Name, Blocks
UNION
SELECT   0 AS Average, Name, LevelID, Blocks
FROM     Levels
WHERE    NOT EXISTS ( SELECT   * 
                      FROM     Reviews
                      WHERE    Levels.LevelID = Reviews.LevelID );


0


source






More articles:


All Articles