Count problem in SQL when I want results from diffrent tabels
- by Nicklas
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.
