SQL Server T-SQL - Query to sum multiple fields (order.total) from one unique cust.number and find most recent order by joining order & cust tables?
Posted on 2011-10-24
I have two tables ( cust and order ) and need to sum all orders from each unique customer number (total (sum) of all orders from that cust.number), of those orders, which one was the most recent order.ship_date"
select distinct order.orderno, order.ship_date, order.ord_total, cust.custnum, cust.lastname, cust.firstname, cust.addr, cust.city, cust.state, cust.zipcode, cust.email from cust
inner join order on cust.custnum = order.custnum
where cust.email <> ' ' and order.ship_date <> ' ' and order.ord_total <> '0.00' and order.ship_date > '2006-11-28'
*** When I run this query it produces many rows for many orders where a customer has ordered multiple orders over the years...so the question is how do I go to "order.ord_total" and sum all orders from one "cust.number" to get the lifetime value for all orders from that customer, and find the order that is most recent from that list of orders from that customer?
--total order.ord_amount for each customer (one customer has many orders)?
--most recent order.ship_date?
Thank you very much for any help/assistance you can provide to help me buid-out my query? Is this stored procedure only? Can you help with that? Group By clause in SQL Server 2005 is killing me...