CODEWITHSUNDEEP
sqlsql-servert-sql
Peter Taylor
Peter Taylor

Reputation: 51

Subquery not grouping returned data correctly

I'm stuck on this query, I have been trying all day to group data based on customer but no luck, can anyone help.

SELECT S.Name AS 'CstName', 

       S.MinPrice - SUM(coalesce(c.Balance,0))  AS 'Blc',

       S.NumberOfItems AS 'Sold'


FROM Cst c, (
SELECT c.Name, c.address, c.Balance, (Min(s.Price)*i.Qty) AS MinPrice, SUM(i.Qty) AS NumberOfItems
FROM             dbo.Cst AS c 
             INNER JOIN
                         dbo.Odr AS o ON c.Name = o.Cust 
             INNER JOIN
                         dbo.inc AS i ON i.Order_no = o.Order_No 
             INNER JOIN
                         dbo.spls AS s ON i.Item = s.Item
WHERE        i.Item = s.Item AND o.Cust =c.Name
group by i.Qty, c.Name, c.Balance, c.address) S


GROUP BY S.Name,S.MinPrice, S.Address, S.Balance, S.NumberOfItems

The results should only show Tom Mary once

CstName   Blc    Sold 
Tom      207.69   1.00 
Tom      210.00  12.00 
Tom      224.45  10.00
Mary     217.47  6.00 
Mary     237.54  12.00

Upvotes: 1

Views: 55

Answers (1)

sqluser
sqluser

Reputation: 5672

Because you want to group by and just display one row per customer, you need to aggregate the columns you are not using in the GROUP BY

So in your SELECT, you should aggregate S.MinPrice and S.NumberOfItems and remove them from your GROUP BY

SELECT S.Name                   AS 'CstName', 

       SUM(S.MinPrice) - SUM(coalesce(c.Balance,0))  AS 'Blc', -- Based on your need you can use any of the aggregation functions

       MAX(S.NumberOfItems)              AS 'Sold' -- Based on your need you can use any of the aggregation functions

...

GROUP BY S.Name

Upvotes: 1

Related Questions