T-SQL get results for a particular order and also the sum of multiple orders, grouped by person

Question

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 16-Aug-23 6:38am

5th LMNt

Updated 14-Sep-23 9:27am

Deeksha Shenoy

v4

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Answer 1 · 2023-08-16T09:20:00

Solution 1

Start here: SQL GROUP BY Statement[^] and the Aggregate Functions[^] that accompany them.

Posted 16-Aug-23 9:20am

OriginalGriff

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.