SQL Server T-SQL - Query to sum multiple fields? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Server T-SQL - Query to sum multiple fields?

Author

Topic

mycoal_2006
Starting Member

1 Post

Posted - 2011-10-24 : 18:05:05

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?

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" for each order 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...

Thank you for your help!

-mycoal_2006@yahoo.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-24 : 23:58:03

[code]
use database
select o.recent_ship_date, o.ord_total, cust.custnum, cust.lastname, cust.firstname, cust.addr, cust.city, cust.state, cust.zipcode, cust.email from cust
inner join
(
select custnum,sum(ord_total) as ord_total,max(ship_date) as recent_ship_date
from order
where ord_total <> '0.00' and ship_date > '2006-11-28'
group by custnum) o
on o.custnum = cust.custnum
where cust.email <> ' '
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mycoal
Starting Member

1 Post

Posted - 2011-10-25 : 17:29:42

visakh16,

It worked! Thank you!

One quick follow-up to get a different point-of-view: what would the code look like to COUNT() all orders within the query for each cust.custnum? So that I would have total order value for that custnum, the total value/amount of all orders from that cust, and then count how many orders are from each cust.custnum?

Thanks again!

-mycoal@live.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-26 : 00:59:27

do you mean this?


select custnum,sum(ord_total) as ord_total,count(order_id) as order_count
from order
where ord_total <> '0.00' and ship_date > '2006-11-28' 
group by custnum

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources