| Author |
 Topic  |
|
|
deanglen
Starting Member
40 Posts |
 Posted - 01/15/2013 : 04:26:21
|
Hi
I 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 example
Customer ID Customer Name Number of Orders
Here is the current code
select distinct o.OrderDate, p.SKU, c.CustomerID, c.Email, c.FirstName, c.LastName from customer c
join dbo.Orders_ShoppingCart os with (NOLOCK) on os.CustomerID = c.CustomerID
join dbo.Product p with (NOLOCK) on p.ProductID = os.ProductID
join dbo.Orders o with (NOLOCK) on o.OrderNumber = os.OrderNumber
and c.IsRegistered = 1
and p.SKU like '%'
order by o.OrderDate, p.SKU, c.CustomerID |
|
|
RickD
Slow But Sure Yak Herding Master
United Kingdom
3560 Posts |
Posted - 01/15/2013 : 05:03:54
|
Something like this:
select c.CustomerID, c.FirstName + ' ' + c.LastName AS CustomerName, count(o.OrderNumber) As NoOrders
from customer c
join dbo.Orders_ShoppingCart os with (NOLOCK) on os.CustomerID = c.CustomerID
join dbo.Product p with (NOLOCK) on p.ProductID = os.ProductID
join dbo.Orders o with (NOLOCK) on o.OrderNumber = os.OrderNumber
and c.IsRegistered = 1
and p.SKU like '%'
GROUP BY c.CustomerID, c.FirstName + ' ' + c.LastName
order by c.CustomerID |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 01/15/2013 : 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 MVP
http://visakhm.blogspot.com/
|
|
|
|
deanglen
Starting Member
40 Posts |
Posted - 01/15/2013 : 06:36:08
|
| Thanks! Worked great! |
|
|
|
RickD
Slow But Sure Yak Herding Master
United Kingdom
3560 Posts |
Posted - 01/15/2013 : 08:06:13
|
I agree with visakh16, I just didn't look..  |
|
|
| |
Topic |
|
Finding out total number of orders
