CODEWITHSUNDEEP
sqlaggregate
Prinzovdarkness
Prinzovdarkness

Reputation: 67

SQL - confused with aggregate

I currently have this query:

select
  customers.emailaddress,
  MAX(orders.orderdate) as "last order"
from orders
join customers
  on orders.customerID = customers.customerID
group by customers.emailaddress

Which gives me the emails, and the last order date. With the 'Orders' table, there is a field named 'PaymentTotal', how can I get this based on the value returned by MAX(orders.orderdate)? (ie am trying to get the amount of the last order per email)

Upvotes: 0

Views: 88

Answers (1)

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171491

select c.EmailAddress, om.MaxOrderDate, o.PaymentTotal
from (
    select CustomerID, MAX(orders.orderdate) as MaxOrderDate
    from orders 
    group by CustomerID
) om
inner join orders o on om.CustomerID = o.CustomerID
    and om.MaxOrderDate = o.orderdate
inner join customers c on o.customerID = c.customerID

Upvotes: 3

Related Questions