Click here to Skip to main content
15,902,299 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I need to return a single row for each customer that purchased a particular product, with the most recent order of that product and the amount of that order, and also the sum of the amounts of all orders with that product for that customer (in a particular date range).

Here's my base FROM clause:
SQL
FROM Customers c
  JOIN Orders o
    ON c.ID = o.CustomerID
  JOIN OrderDetails od
    ON o.ID = od.OrderID
    AND od.ProductID = 111

Simplified data set:
c.ID | c.Name
-------------
1    | Bob
2    | Jane
3    | Bill
4    | Mary

o.ID | o.CustomerID | o.Date
--------------------------------
10   | 1            | 6/10/2021
11   | 3            | 8/1/2021
12   | 2            | 8/4/2021
13   | 4            | 10/20/2022
14   | 1            | 11/18/2022
15   | 4            | 2/1/2023
16   | 4            | 4/12/2023

od.ID | od.OrderID | od.ProductID | od.Amount
---------------------------------------------
100   | 10         | 111          | 100
101   | 11         | 111          | 50
102   | 12         | 111          | 25
103   | 13         | 111          | 10
104   | 14         | 111          | 300
105   | 15         | 111          | 120
106   | 16         | 111          | 60

Expected results:
c.ID | c.Name | LatestOrder | LatestOrderAmount | TotalAllAmounts
-----------------------------------------------------------------
1    | Bob    | 11/18/2022  | 300               | 400
2    | Jane   | 8/4/2021    | 25                | 25
3    | Bill   | 8/1/2021    | 50                | 50
4    | Mary   | 4/12/2023   | 60                | 190 


What I have tried:

I tried adding more joins with subqueries, and it ends up almost working, but it either doesn't sum all the orders or it returns one row per order rather than per customer, etc.

I think the answer from https://stackoverflow.com/questions/54923664/t-sql-im-trying-to-get-the-latest-row-of-a-column-but-also-the-sum-of-another is on the right track, but I can't figure out how to put it all together.
Posted
Updated 14-Sep-23 9:27am
v4

1 solution

Start here: SQL GROUP BY Statement[^] and the Aggregate Functions[^] that accompany them.
 
Share this answer
 
Comments
5th LMNt 16-Aug-23 16:25pm    
That doesn't work because when you get the sum, you have to group by all the other stuff, including the amount from the order with max date, so it returns one row per order (specifically one row per $X amount) and then only sums that order because it's counted as a separate grouping. You can do a max date and a sum of amounts, but putting the single amount that corresponds to the max-date order also throws it off. I think I need to work with partitioning and row numbers (the link I included shows how you can do a descending date order and choose row number 1 to get that info and then also partition without row numbers to get the sum piece). But I don't know how to write it properly using multiple tables.
OriginalGriff 16-Aug-23 16:44pm    
:sigh:

You don't group by everything: you use group by to get the groups and the aggregate values - you then JOIN those back to the rest of the data you need.

See here: SQL GROUP By and the "Column 'name' is invalid in the select list because..." error[^] - it explains the basics.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900