CODEWITHSUNDEEP
sqlsql-server
Viking22
Viking22

Reputation: 561

SQL aggregate in a custom way

Query on SQL Server Grouping. Using SQL Server 2012.

We have a couple of tables - say Orders and Order Details (1..* relationship). Suppose the Orders table has the following cols: OrderID,OrderDate and OrderDetails has OrderID, OrderDetailID, ProductID, IsGift (Has Y, N values), GiftAmt How can I query these tables to get all the Orders along with a HasGift column that will be Y if at least one OrderDetail has IsGift=Y in that Order, Otherwise HasGift will be N.

For e.g.

OrderID  Date
1        2020-05-06
2        2020-06-22
3        2020-07-02

OrderID  OrderDetailID    ProductID    IsGift     GiftAmt
1        1                123          N          NULL
1        2                356          N          NULL
2        1                335          Y          10
2        2                993          N          NULL
3        1                377          Y          10
3        2                453          N          NULL
3        3                343          Y          50
3        4                565          N          NULL

Expected o/p:
OrderID    OrderDate    HasGift     GiftSum 
1          2020-05-06   N           NULL
2          2020-06-22   Y           10
3          2020-07-02   Y           60

Upvotes: 0

Views: 44

Answers (1)

rinz1er
rinz1er

Reputation: 449

SELECT o.OrderID, 
       o.Date AS OrderDate
       COALESCE(MAX(od.IsGift), 'N') AS HasGift,
       SUM(od.GiftAmt) AS GiftSum
FROM Orders o LEFT JOIN OrderDetails od 
     ON o.OrderID = od.OrderID AND od.IsGift = 'Y'
GROUP BY o.OrderID, o.Date

Upvotes: 2

Related Questions