CODEWITHSUNDEEP
sqlsql-servercommon-table-expression
user1999544
user1999544

Reputation: 23

CTE with Group By returning the same results as basic query

I'm trying to select a count of product reviews by rating, where the rating can be 0 - 5.

The following basic select works but won't give a count of ratings that don't exist in the underlying table.

SELECT Rating, COUNT(*)  AS 'Reviews' FROM  ProductReviews 
WHERE   ProductID = 'product1' 
GROUP BY Rating

I've tried using a CTE to generate the missing results, joined to Reviews table using an outer join, as soon as I try to include the "group by" expression the results fall back to match the results from the basic query.

(I've checked that the CTE does indeed generate the full range of required values).

BEGIN

DECLARE @START AS INT = 0;
DECLARE @END AS INT = 5;

WITH CTE_Ratings AS
(
    SELECT @START as cte_rating
    UNION ALL
    SELECT 1 + cte_rating
    FROM CTE_Ratings
    WHERE cte_rating < @END
)


SELECT
    cte_rating AS 'ReviewRating'
    , ISNULL(COUNT(*), 0) AS 'ReviewCount'
FROM CTE_Ratings
LEFT OUTER JOIN Reviews ON Reviews.Rating = cte_rating
WHERE ProductReviews.ProductID = 'product1'
    AND cte_rating BETWEEN @START AND @END
    GROUP BY cte_rating
END

(I also tried building a temporary table containing the required values, joined to the Reviews table, with identical results).

In the case of both of the above queries the results are:

Rating  Reviews
0       1
3       3
4       9
5       47

Whereas what I'm trying to get to for the same data is:

Rating  Reviews
0       1
1       0
2       0
3       3
4       9
5       47

Can anyone suggest when the addition of the Group By aggregate function is causing the query to fail, or how it might be improved ?

Upvotes: 2

Views: 2697

Answers (1)

gbn
gbn

Reputation: 432210

The WHERE changes the OUTER JOIN to an INNER JOIN because you are filtering on optional rows from the outer table.
So, move the outer table filter into the JOIN

Also, you are already filtering between start and end in the CTE

WITH ...
-- CTE here
SELECT
    C.cte_rating AS 'ReviewRating'
    , ISNULL(COUNT(PR.Rating), 0) AS 'ReviewCount'
FROM
   CTE_Ratings C
   LEFT OUTER JOIN
   ProductReviews PR ON C.cte_rating= PR.Rating AND PR.ProductID = 'product1'
GROUP BY
   C.cte_rating

More clearly, you are actually doing this

WITH ...
-- CTE here
SELECT
    C.cte_rating AS 'ReviewRating'
    , ISNULL(COUNT(PR.Rating), 0) AS 'ReviewCount'
FROM
   CTE_Ratings C
   LEFT OUTER JOIN
   (
      SELECT PR.Rating
      FROM ProductReviews
      WHERE ProductID = 'product1'
   ) PR ON C.cte_rating= PR.Rating
GROUP BY
   C.cte_rating

Upvotes: 2

Related Questions