SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Finding orders per customer
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

raindear
Yak Posting Veteran

64 Posts

Posted - 01/22/2014 :  12:03:17  Show Profile  Reply with Quote
Hi

I have three SQL tables that I am trying to join. One is Called Customer and the other Orders.

Customer contains these fields that I need

CustomerID,Email,FirstName,LastName,

Orders has these that i need

OrderNumber,CustomerID,OrderTotal,OrderSubTotal

OrdersShoppingCart has these that I need

OrderNumber,CustomerID,OrderedProductSKU,OrderedProductName,OrderedProductPrice

What I would like to try to export is this

CustomerID
FirstName
LastName
Order ID
SKU
Order Total

This would be grouped for each customer for all total orders. Do you think this is possible?

Would be awesome If I could get some guidance.

bitsmed
Constraint Violating Yak Guru

405 Posts

Posted - 01/22/2014 :  16:58:13  Show Profile  Reply with Quote
This query will show the output you requested, but will not group anything, as I'm not sure what you mean by "This would be grouped for each customer for all total orders".
select c.CustomerID
      ,c.FirstName
      ,c.LastName
      ,o.OrderNumber as OrderID
      ,osc.OrderedProductSKU as SKU
      ,o.OrderTotal
  from Customer as c
       inner join Orders as o
               on o.CustomerID=c.CustomerID
       inner join OrdersShoppingCart as osc
               on osc.OrderNumber=o.OrderNumber
              and osc.CustomerID=o.CustomerID
 order by c.CustomerID
         ,o.OrderNumber
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/23/2014 :  06:39:57  Show Profile  Reply with Quote
as i understand this may be what you want

SELECT CustomerID,
FirstName,
LastName,
COALESCE(OrderIDCnt,0),
COALESCE(SKUCnt,0),
COALESCE(OrderTotal,0)
FROM Customer as c
left join (SELECT o.CustomerID,
           SUM(OrderTotal) AS OrderTotal,
           COUNT(OrderedProductSKU) AS SKUCnt,
           COUNT(OrderID) AS OrderIDCnt
           FROM Orders as o
           inner join OrdersShoppingCart as osc
           on osc.OrderNumber=o.OrderNumber
           and osc.CustomerID=o.CustomerID
           GROUP BY o.CustomerID
          )ord
ON ord.CustomerID = c.CustomerID


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

raindear
Yak Posting Veteran

64 Posts

Posted - 01/23/2014 :  08:31:13  Show Profile  Reply with Quote
Thanks. First one works fine but I'd like them grouped as a collective per customer. Second SQL I get this error

Msg 207, Level 16, State 1, Line 11
Invalid column name 'OrderID'.
Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'CustomerID'.

UPDATE -

I did get it to work almost using this

SELECT c.CustomerID, -- Add c. in front of CustomerID
FirstName,
LastName,
COALESCE(OrderIDCnt,0)AS Orders,
COALESCE(OrderTotal,0)AS OrderTotal
FROM Customer as c
left join (SELECT o.CustomerID,
SUM(OrderTotal) AS OrderTotal,
COUNT(o.OrderNumber) AS OrderIDCnt -- Disambiguated OrderID
FROM Orders as o
inner join Orders_ShoppingCart as osc
on osc.OrderNumber=o.OrderNumber
and osc.CustomerID=o.CustomerID
and O.OrderDate between '01-01-2013' and '01-22-2014
GROUP BY o.CustomerID
)ord
ON ord.CustomerID = c.CustomerID

But how would I get it to only show those records with a combined OrderTotal of greater than 0? It is currently showing those with no orders also

Edited by - raindear on 01/23/2014 12:24:09
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

405 Posts

Posted - 01/23/2014 :  12:23:26  Show Profile  Reply with Quote
quote:
Originally posted by raindear

I'd like them grouped as a collective per customer.

Please provide input and output samples.
Go to Top of Page

raindear
Yak Posting Veteran

64 Posts

Posted - 01/23/2014 :  12:47:25  Show Profile  Reply with Quote
Ok using your method I have something like this

Customer ID FirstName LastName OrderID SKU OrderTotal
----------- --------- -------- ------- ---- ----------

123 Alan Smith 123 ABC 5.66
123 Alan Smith 124 EFG 5.44
123 Alan Smith 125 HIJ 9.99
123 Alan Smith 126 JMG 10.43
123 Alan Smith 127 ZZZ 99.99

Which is great! But what I am trying to do is to combine them so that it all comes under a total for each customer so that somehow it displays something like

Customer ID FirstName LastName OrderID SKU OrderTotal
----------- --------- -------- ------- ---- ----------

123 Alan Smith 123,124,125,126,127, ABC,EFG,HIJ,JMG,ZZZ 131.51

So like a grand total for each customer?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/23/2014 :  13:02:26  Show Profile  Reply with Quote
quote:
Originally posted by raindear

Thanks. First one works fine but I'd like them grouped as a collective per customer. Second SQL I get this error

Msg 207, Level 16, State 1, Line 11
Invalid column name 'OrderID'.
Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'CustomerID'.

UPDATE -

I did get it to work almost using this

SELECT c.CustomerID, -- Add c. in front of CustomerID
FirstName,
LastName,
COALESCE(OrderIDCnt,0)AS Orders,
COALESCE(OrderTotal,0)AS OrderTotal
FROM Customer as c
left join (SELECT o.CustomerID,
SUM(OrderTotal) AS OrderTotal,
COUNT(o.OrderNumber) AS OrderIDCnt -- Disambiguated OrderID
FROM Orders as o
inner join Orders_ShoppingCart as osc
on osc.OrderNumber=o.OrderNumber
and osc.CustomerID=o.CustomerID
and O.OrderDate between '01-01-2013' and '01-22-2014
GROUP BY o.CustomerID
)ord
ON ord.CustomerID = c.CustomerID

But how would I get it to only show those records with a combined OrderTotal of greater than 0? It is currently showing those with no orders also




SELECT c.CustomerID, -- Add c. in front of CustomerID
FirstName,
LastName,
COALESCE(OrderIDCnt,0)AS Orders,
COALESCE(OrderTotal,0)AS OrderTotal
FROM Customer as c
inner join (SELECT o.CustomerID,
SUM(OrderTotal) AS OrderTotal,
COUNT(o.OrderNumber) AS OrderIDCnt -- Disambiguated OrderID
FROM Orders as o
inner join Orders_ShoppingCart as osc
on osc.OrderNumber=o.OrderNumber
and osc.CustomerID=o.CustomerID
and O.OrderDate between '01-01-2013' and '01-22-2014
GROUP BY o.CustomerID
)ord
ON ord.CustomerID = c.CustomerID
WHERE OrderTotal > 0


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

raindear
Yak Posting Veteran

64 Posts

Posted - 01/24/2014 :  07:57:20  Show Profile  Reply with Quote
Ok I finally have the SQL that I wanted!

select c.CustomerID
,c.FirstName
,c.LastName
,c.Email
,o.OrderNumber as OrderID
,o.OrderDate
,osc.OrderedProductSKU as SKU
,osc.OrderedProductName as Name
,ct.Name as Category
,o.OrderSubtotal
from Customer as c
inner join Orders as o
on o.CustomerID=c.CustomerID
inner join Orders_ShoppingCart as osc
on osc.OrderNumber=o.OrderNumber
inner join ProductCategory as pc
on osc.ProductID=pc.ProductID
inner join Category as ct
on pc.CategoryID=ct.CategoryID
and O.OrderDate between '01-01-2013' and '01-22-2014'
order by c.CustomerID
,o.OrderNumber

But when I try to group this I get Msg 8120, Level 16, State 1, Line 1
Column 'Customer.CustomerID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

How can I sort these? Currently each item that is ordered is appearing on a seperate line.
Go to Top of Page

sqlsaga
Yak Posting Veteran

USA
93 Posts

Posted - 01/24/2014 :  12:35:29  Show Profile  Reply with Quote
quote:
Originally posted by raindear

Ok I finally have the SQL that I wanted!

select c.CustomerID
,c.FirstName
,c.LastName
,c.Email
,o.OrderNumber as OrderID
,o.OrderDate
,osc.OrderedProductSKU as SKU
,osc.OrderedProductName as Name
,ct.Name as Category
,o.OrderSubtotal
from Customer as c
inner join Orders as o
on o.CustomerID=c.CustomerID
inner join Orders_ShoppingCart as osc
on osc.OrderNumber=o.OrderNumber
inner join ProductCategory as pc
on osc.ProductID=pc.ProductID
inner join Category as ct
on pc.CategoryID=ct.CategoryID
and O.OrderDate between '01-01-2013' and '01-22-2014'
order by c.CustomerID
,o.OrderNumber

But when I try to group this I get Msg 8120, Level 16, State 1, Line 1
Column 'Customer.CustomerID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

How can I sort these? Currently each item that is ordered is appearing on a seperate line.




If you use an Aggregate function like COUNT, SUM, AVG, MIN, MAX etc you have to use the rest of your columns you are selecting in the Group By Clause which means you are asking sql server to group the data based on all those columns.
Ex:
SELECT CUSTOMERID, SUM(ItemQty)
FROM Sales
GROUP BY CustomerID


Visit www.sqlsaga.com for more t-sql snippets and BI related how to's.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/27/2014 :  07:31:14  Show Profile  Reply with Quote
quote:
Originally posted by raindear

Ok I finally have the SQL that I wanted!

select c.CustomerID
,c.FirstName
,c.LastName
,c.Email
,o.OrderNumber as OrderID
,o.OrderDate
,osc.OrderedProductSKU as SKU
,osc.OrderedProductName as Name
,ct.Name as Category
,o.OrderSubtotal
from Customer as c
inner join Orders as o
on o.CustomerID=c.CustomerID
inner join Orders_ShoppingCart as osc
on osc.OrderNumber=o.OrderNumber
inner join ProductCategory as pc
on osc.ProductID=pc.ProductID
inner join Category as ct
on pc.CategoryID=ct.CategoryID
and O.OrderDate between '01-01-2013' and '01-22-2014'
order by c.CustomerID
,o.OrderNumber

But when I try to group this I get Msg 8120, Level 16, State 1, Line 1
Column 'Customer.CustomerID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

How can I sort these? Currently each item that is ordered is appearing on a seperate line.



refer to my suggestion. i bring it grouped and aggregated so that you just need a join to it.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000