?
Solved

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
7
Medium Priority
?
451 Views
Last Modified: 2012-05-12

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"

use database
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...
0
Comment
Question by:mycoal_2006
6 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 37021339
How does this do for you?
0
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 1000 total points
ID: 37021351
I thought I hit "Attach" last time!
select Max(order.orderno), Max(order.ship_date), Sum(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' 
group by cust.custnum, cust.lastname,  cust.firstname, cust.addr, cust.city, cust.state, cust.zipcode, cust.email

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37021509
Is the orderno consecutive?  In other words, will the most recent orderno also have the most recent ship date?
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 37021645
My solution hopes so, doesn't it!
0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1000 total points
ID: 37025733
Below should do it, I hope, but it can be refined as needed, of course.
select 
    o.max_orderno as orderno, o.sum_ord_total as all_orders_total, 
    o2.ship_date as last_order_ship_date, o2.ord_total as last_order_total, 
    c.custnum, c.lastname, c.firstname, c.addr, c.city, c.state, c.zipcode, c.email 
from cust c
inner join (
    select custnum, SUM(ord_total) AS sum_ord_total, MAX(orderno) AS max_orderno
    from [order]
    where ship_date <> ' ' and ord_total <> '0.00' and ship_date > '20061128' 
    group by custnum
) AS o on c.custnum = o.custnum
inner join [order] o2 on o2.orderno = o.max_orderno
where c.email <> ' '

Open in new window

0
 
LVL 101

Expert Comment

by:mlmcc
ID: 37181741
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question