SQL Server problems when using ODER BY and DISTINCT together

Question

I got two problems, the first problem is my two COUNTS that I start with. GroupID is a string that keep products together (Name_Year together), same product but different size. If I have three reviews in tblReview and they all have the same GroupID I want to return 3. My problem is that if I have three Products with different ProductID but same GroupID and I add three Review to that GroupID I got 9 returns (3*3). If I only have one Product With the same GroupID and three Reviews it works (1*3=3 returns)

The Second problem is that if I have the ORDER BY CASE Price I have to add GROUP BY Price as well and then I don't get the DISTINCT effect that I want. And that is to just show products that have unique GroupID.

Here's the query, hope somebody can help me with this.

ALTER PROCEDURE GetFilterdProducts
@CategoryID INT, @ColumnName varchar(100)

AS
SELECT   COUNT(tblReview.GroupID) AS ReviewCount, 
    COUNT(tblComment.GroupID) AS CommentCount, 
    Product.ProductID, 
    Product.Name,
    Product.Year,
    Product.Price,
    Product.BrandID, 
                Product.GroupID, 
                AVG(tblReview.Grade) AS Grade


FROM            Product LEFT JOIN
                         tblComment ON Product.GroupID = tblComment.GroupID LEFT JOIN
                         tblReview ON Product.GroupID = tblReview.GroupID


WHERE        (Product.CategoryID = @CategoryID)

GROUP BY Product.ProductID, Product.BrandID, Product.GroupID, Product.Name, Product.Year, Product.Price

HAVING COUNT(distinct Product.GroupID) = 1

ORDER BY
  CASE 
  WHEN @ColumnName='Name' THEN Name
  WHEN @ColumnName='Year' THEN Year
  WHEN @ColumnName='Price' THEN Price 
  END

My tabels:

Product: ProductID, Name, Year, Price, BrandID, GroupID

tblReview: ReviewID, Description, Grade, ProductID, GroupID

tblComment: CommentID, Description, ProductID, GroupID

I think that my problem is that if I have three GroupID with the same name, ex Nike_2010 in Product and I have three Reviews in tblReview that counts the first row in Products that contain Nike_2010 counts how many reviews in tblReview with the same GroupID, Nike_2010 and then the second row in Product that contains Nike_2010 and then do the same count again and again, that results to 9 rows. How do I avoid that?

Answer 1

For starters, because you're joining on multiple tables, you're going to end up with the cross product of all of them as a result. Your counts will then return the total count of rows containing data in that column. Consider the following example:

- PRODUCTS -   -- COMMENTS --    --- REVIEWS ---
 Key | Name     Key | Comment     Key | Review
 1   | A        1   | Foo         1   | Great
 2   | B        1   | Bar         1   | Wonderful

The query

SELECT PRODUCTS.Key, PRODUCTS.Name, COMMENTS.Comment, REVIEWS.Review
FROM PRODUCTS
LEFT OUTER JOIN COMMENTS ON PRODUCTS.KEY = COMMENTS.KEY
LEFT OUTER JOIN REVIEWS ON PRODUCTS.KEY = REVIEWS.KEY

will result in the following data:

 Key | Name | Comment | Review
 1   | A    | Foo     | Great
 1   | A    | Foo     | Wonderful
 1   | A    | Bar     | Great
 1   | A    | Bar     | Wonderful
 2   | B    | NULL    | NULL

Thus, counting in this format

SELECT PRODUCTS.Key, PRODUCTS.Name, COUNT(COMMENTS.Comment), COUNT(REVIEWS.Review)
FROM PRODUCTS
LEFT OUTER JOIN COMMENTS ON PRODUCTS.KEY = COMMENTS.KEY
LEFT OUTER JOIN REVIEWS ON PRODUCTS.KEY = REVIEWS.KEY
GROUP BY PRODUCTS.Key, PRODUCTS.Name

will give you

Key | Name | Count1 | Count2
1   | A    | 4      | 4
2   | B    | 0      | 0

because it's counting each row in the table produced by the join!

Instead, you want to count each table separately in a subquery before joining it back like the following:

SELECT PRODUCTS.Key, PRODUCTS.Name, ISNULL(CommentCount.NumComments, 0),
       ISNULL(ReviewCount.NumReviews, 0)
FROM PRODUCTS
LEFT OUTER JOIN (SELECT Key, COUNT(*) as NumComments
                 FROM COMMENTS
                 GROUP BY Key) CommentCount on PRODUCTS.Key = CommentCount.Key
LEFT OUTER JOIN (SELECT Key, COUNT(*) as NumReviews
                 FROM REVIEWS
                 GROUP BY Key) ReviewCount on PRODUCTS.Key = ReviewCount.Key

which will produce the following

Key | Name | NumComments | NumReviews
1   | A    | 2           | 2
2   | B    | 0           | 0

---

As for the "DISTINCT effect" you refer to, I'm not exactly sure I follow. Could you elaborate a bit?

Answer 2

About second problem - cannot you group by same CASE statement? You shouldn't have Price field in results list then though.