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
 Finding orders per customer

Author  Topic 

raindear
Yak Posting Veteran

64 Posts

Posted - 2014-01-22 : 12:03:17
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
Aged Yak Warrior

545 Posts

Posted - 2014-01-22 : 16:58:13
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

52326 Posts

Posted - 2014-01-23 : 06:39:57
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 - 2014-01-23 : 08:31:13
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
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-01-23 : 12:23:26
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 - 2014-01-23 : 12:47:25
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

52326 Posts

Posted - 2014-01-23 : 13:02:26
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 - 2014-01-24 : 07:57:20
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

93 Posts

Posted - 2014-01-24 : 12:35:29
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

52326 Posts

Posted - 2014-01-27 : 07:31:14
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
   

- Advertisement -