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 |
deanglen
Yak Posting Veteran
65 Posts |
Posted - 2013-01-15 : 04:26:21
|
HiI have this script that finds extracts a full table, row by row, of each order from our e-commerce site. Do you know how I can change this so it gives a number of orders per customer?For exampleCustomer ID Customer Name Number of OrdersHere is the current codeselect distinct o.OrderDate, p.SKU, c.CustomerID, c.Email, c.FirstName, c.LastName from customer cjoin dbo.Orders_ShoppingCart os with (NOLOCK) on os.CustomerID = c.CustomerIDjoin dbo.Product p with (NOLOCK) on p.ProductID = os.ProductIDjoin dbo.Orders o with (NOLOCK) on o.OrderNumber = os.OrderNumberand c.IsRegistered = 1and p.SKU like '%'order by o.OrderDate, p.SKU, c.CustomerID |
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2013-01-15 : 05:03:54
|
Something like this:select c.CustomerID, c.FirstName + ' ' + c.LastName AS CustomerName, count(o.OrderNumber) As NoOrdersfrom customer cjoin dbo.Orders_ShoppingCart os with (NOLOCK) on os.CustomerID = c.CustomerIDjoin dbo.Product p with (NOLOCK) on p.ProductID = os.ProductIDjoin dbo.Orders o with (NOLOCK) on o.OrderNumber = os.OrderNumberand c.IsRegistered = 1and p.SKU like '%'GROUP BY c.CustomerID, c.FirstName + ' ' + c.LastNameorder by c.CustomerID |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-15 : 05:52:53
|
I prefer p.SKU > = '' over the currently written condition p.SKU like '%' so as to take advantage of an available index if present on SKU field------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
deanglen
Yak Posting Veteran
65 Posts |
Posted - 2013-01-15 : 06:36:08
|
Thanks! Worked great! |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2013-01-15 : 08:06:13
|
I agree with visakh16, I just didn't look.. |
|
|
|
|
|
|
|