Count problem in SQL when I want results from diffrent tabels

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.

---

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.

---

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, 
       ^^^^^^^^