CODEWITHSUNDEEP
sql-server
user1007103
user1007103

Reputation: 425

Select Distinct Sum SQL Server

I have a problem with the SUM(tblReview.GradePoint) I get 6 as result for GroupID Ballon because I have three products with GroupID Ballon, but I want the result to be 2 because there is only one review with that groupID in tblReview, how can I do that?

SELECT      Product.GroupID,
            max(Product.ProductID) as ProductID,
            max (Product.BrandID) as BrandID,
            max (Product.Year) as Year,
            max (Product.Name) as Name,
            max (tblBrand.BrandName)as BrandName,
            SUM(tblReview.GradePoint) as GradePoint

FROM        Product INNER JOIN
            tblBrand ON Product.BrandID = tblBrand.BrandID LEFT OUTER JOIN
            tblReview ON Product.GroupID = tblReview.GroupID

GROUP BY Product.GroupID


HAVING COUNT(distinct Product.GroupID) = 1

ORDER BY GradePoint DESC

Product
ProductID   GroupID     BrandID
--------------------------------------
 1          Ballon      10
 2          Ballon      10
 3          Ballon      10
 4          Ball        21
 5          Ball        21
 6          Chess       2
 7          Chess       2
 8          Hat         30


tblReview    
ProductID   GroupID     GradePoint
------------------------------------------
 2           Ballon     2
 4           Ball       1
 5           Ball       1
 5           Ball       1
 6           Chess      -1
 8           Hat        1


tblBrand    
BrandID     ProductID
-----------------------
 10         1
 10         2
 10         3
 21         4
 21         5
 2          6
 2          7
 30         8

Upvotes: 1

Views: 829

Answers (1)

Krzysztof
Krzysztof

Reputation: 16130

Try this:

SELECT      Product.GroupID,
            max(Product.ProductID) as ProductID,
            max (Product.BrandID) as BrandID,
            max (Product.Year) as Year,
            max (Product.Name) as Name,
            max (tblBrand.BrandName)as BrandName,
            max(tblReview.GradePoint) as GradePoint

FROM        Product INNER JOIN
            tblBrand ON Product.BrandID = tblBrand.BrandID LEFT OUTER JOIN
            (SELECT GroupID, SUM(GradePoint) GradePoint FROM tblReview GROUP BY GroupID) tblReview ON Product.GroupID = tblReview.GroupID

GROUP BY Product.GroupID

Upvotes: 2

Related Questions