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.
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 needOrderNumber,CustomerID,OrderTotal,OrderSubTotalOrdersShoppingCart has these that I needOrderNumber,CustomerID,OrderedProductSKU,OrderedProductName,OrderedProductPriceWhat I would like to try to export is thisCustomerIDFirstNameLastNameOrder IDSKUOrder TotalThis 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 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-01-23 : 06:39:57
|
as i understand this may be what you wantSELECT CustomerID,FirstName,LastName,COALESCE(OrderIDCnt,0),COALESCE(SKUCnt,0),COALESCE(OrderTotal,0)FROM Customer as cleft 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 )ordON ord.CustomerID = c.CustomerID ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 errorMsg 207, Level 16, State 1, Line 11Invalid column name 'OrderID'.Msg 209, Level 16, State 1, Line 1Ambiguous column name 'CustomerID'.UPDATE - I did get it to work almost using thisSELECT c.CustomerID, -- Add c. in front of CustomerIDFirstName,LastName,COALESCE(OrderIDCnt,0)AS Orders,COALESCE(OrderTotal,0)AS OrderTotalFROM Customer as cleft 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 )ordON ord.CustomerID = c.CustomerIDBut 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 |
|
|
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. |
|
|
raindear
Yak Posting Veteran
64 Posts |
Posted - 2014-01-23 : 12:47:25
|
Ok using your method I have something like thisCustomer ID FirstName LastName OrderID SKU OrderTotal----------- --------- -------- ------- ---- ----------123 Alan Smith 123 ABC 5.66123 Alan Smith 124 EFG 5.44123 Alan Smith 125 HIJ 9.99123 Alan Smith 126 JMG 10.43123 Alan Smith 127 ZZZ 99.99Which 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 likeCustomer ID FirstName LastName OrderID SKU OrderTotal----------- --------- -------- ------- ---- ----------123 Alan Smith 123,124,125,126,127, ABC,EFG,HIJ,JMG,ZZZ 131.51So like a grand total for each customer? |
|
|
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 errorMsg 207, Level 16, State 1, Line 11Invalid column name 'OrderID'.Msg 209, Level 16, State 1, Line 1Ambiguous column name 'CustomerID'.UPDATE - I did get it to work almost using thisSELECT c.CustomerID, -- Add c. in front of CustomerIDFirstName,LastName,COALESCE(OrderIDCnt,0)AS Orders,COALESCE(OrderTotal,0)AS OrderTotalFROM Customer as cleft 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 )ordON ord.CustomerID = c.CustomerIDBut 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 CustomerIDFirstName,LastName,COALESCE(OrderIDCnt,0)AS Orders,COALESCE(OrderTotal,0)AS OrderTotalFROM Customer as cinner join (SELECT o.CustomerID,SUM(OrderTotal) AS OrderTotal,COUNT(o.OrderNumber) AS OrderIDCnt -- Disambiguated OrderIDFROM Orders as oinner join Orders_ShoppingCart as oscon osc.OrderNumber=o.OrderNumberand osc.CustomerID=o.CustomerIDand O.OrderDate between '01-01-2013' and '01-22-2014GROUP BY o.CustomerID)ordON ord.CustomerID = c.CustomerIDWHERE OrderTotal > 0 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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.OrderNumberBut when I try to group this I get Msg 8120, Level 16, State 1, Line 1Column '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. |
|
|
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.OrderNumberBut when I try to group this I get Msg 8120, Level 16, State 1, Line 1Column '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 SalesGROUP BY CustomerIDVisit www.sqlsaga.com for more t-sql snippets and BI related how to's. |
|
|
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.OrderNumberBut when I try to group this I get Msg 8120, Level 16, State 1, Line 1Column '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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|