Count problem in SQL when I want results from diffrent tabels

Question

ALTER PROCEDURE GetProducts
@CategoryID INT

AS

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

        FROM tblReview, tblComment, Product

WHERE   (Product.CategoryID = @CategoryID)

GROUP BY Product.GroupID

HAVING COUNT(distinct Product.GroupID) = 1

This is what the tabels look like:

        **Product**  |**tblReview**  |   **tblComment**

        ProductID   |   ReviewID    |   CommentID
        Name        |   Description |   Description
        Year        |   GroupID     |   GroupID
        Price       |   Grade       |   
        GroupID                 

GroupID is name_year of a Product, ex Nike_2010. One product can have diffrent sizes for exampel:

ProductID  |  Name |  Year |  Price |  Size | GroupID

   1       |  Nike |  2010 |  50    |   8   | Nike_2010
   2       |  Nike |  2010 |  50    |   9   | Nike_2010
   3       |  Nike |  2010 |  50    |   10  | Nike_2010
   4       | Adidas|  2009 |  45    |   8   | Adidas_2009
   5       | Adidas|  2009 |  45    |   9   | Adidas_2009
   6       | Adidas|  2009 |  45    |   10  | Adidas_2009

I dont get the right count in my tblReview and tblComment. If I add a review to Nike size 8 and I add one review to Nike size 10 I want 2 count results when I list the products with diffrent GroupID. Now I get the same count on Reviews and Comment and both are wrong.

I use a datalist to show all the products with diffrent/unique GroupID, I want it to be like this:

    ______________
   |              |
   |  Name: Nike  |
   |  Year: 2010  |
   |  (All Sizes) |
   |  x Reviews   |
   |  x Comments  |
   |  x AVG Grade |
   |______________|

All Reviewcounts, Commentcounts and the Average of all products with the same GroupID, the Average works great.

Answer 1

There will be one comment row for every product, so both COUNT(tblReview.GroupID) and COUNT(tblComment.GroupID) will return the number of products x number of comments for that group.

Another way of explaining that is by running the query without a group by. The database will iterate over the rows, and increase COUNT(tblReview.GroupID) for every row where tblReview.GroupID is not null.

One solution is to use distinct. Change the ReviewCount to:

 COUNT(DISTINCT tblReview.GroupID) AS ReviewCount, 
       ^^^^^^^^

Answer 2

Because you are not specifying any criteria which join the tables, you will get the product category you specify in combination with every of tblReview and tblComment (effectively a massive cross join).

Your AVG just happens to work out of luck.

You should try something like this:

SELECT  (SELECT COUNT(*) FROM tblReview WHERE tblReview.GroupID = Product.GroupID) AS ReviewCount, 
        (SELECT COUNT(*) FROM tblComment WHERE tblComment.GroupID = Product.GroupID) AS CommentCount,
        Product.GroupID,
        MAX(Product.ProductID) AS ProductID,
        AVG(Product.Price) AS Price,
        MAX (Product.Year) AS Year,
        MAX (Product.Name) AS Name,
        (SELECT AVG(tblReview.Grade) FROM tblReview WHERE tblReview.GroupID = Product.GroupID) AS Grade
        FROM Product
WHERE   (Product.CategoryID = @CategoryID)
GROUP BY Product.GroupID
HAVING COUNT(distinct Product.GroupID) = 1

Normally I would not use correlated subqueries and instead join to aggregate subqueries, but this is more illustrative of your problem.