SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Order number grouping and count


Order number grouping and count

Author
Message
stamic
stamic
SSC Veteran
SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)

Group: General Forum Members
Points: 279 Visits: 482
I have two tables. Generically speaking, the first table has customer_number, item_number. The second has order_number, item_number, customer_number. The first table does not have all the customers and items from the second table.

I need to get a count of customers by order number for each customer in the first table. Meaning, I need to know how many customers and item numbers in the first table came from their first order, or their second order or their third order etc...If there are 15 customers and items, but 10 were ordered on their first visit and five were ordered on their second visit (the five ordered something else on their first visit), I think my results would look like:

customers____________ vist_when_order_was_placed
10___________________1
5____________________2


...but I can't quite get there.

Anyone?

Thanks!
noeld
noeld
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10154 Visits: 2048
Can you post sample data and DDL of your tables Wink


* Noel
stamic
stamic
SSC Veteran
SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)

Group: General Forum Members
Points: 279 Visits: 482
-- Although I used sequential CUSTOMER_ID's here
-- the unique identifier for each customer is LOCATION_ID + CUSTOMER_ID
-- There can be a CUSTOMER_ID of 1 at each location. ITEM is also
-- unique at each location. There is no order number in #SPECIAL_ORDERS.
-- I need to show that the first 10 customers in #SPECIAL_ORDERS
-- came from their first vist and the last 5 came from their second visit,
-- either by date or order number, I don't know.
CREATE TABLE #ORDERS(
LOCATION_ID INT
,ORDER_NO INT
,ITEM_NO INT
,CUSTOMER_ID INT
,VISIT_DATE DATETIME)

CREATE TABLE #SPECIAL_ORDERS(
LOCATION_ID INT
,ITEM_NO INT
,CUSTOMER_ID INT
,VISIT_DATE DATETIME)

INSERT INTO #ORDERS VALUES(1,1,2,1, '2009-01-01 00:00:00:000')
INSERT INTO #ORDERS VALUES(1,2,2,2, '2009-01-01 00:00:00:000')
INSERT INTO #ORDERS VALUES(1,3,2,3, '2009-01-01 00:00:00:000')
INSERT INTO #ORDERS VALUES(1,4,2,4, '2009-01-01 00:00:00:000')
INSERT INTO #ORDERS VALUES(1,5,2,5, '2009-01-01 00:00:00:000')
INSERT INTO #ORDERS VALUES(1,6,2,6, '2009-01-01 00:00:00:000')
INSERT INTO #ORDERS VALUES(2,1,2,7, '2009-01-01 00:00:00:000')
INSERT INTO #ORDERS VALUES(2,2,2,8, '2009-01-01 00:00:00:000')
INSERT INTO #ORDERS VALUES(2,3,2,9, '2009-01-01 00:00:00:000')
INSERT INTO #ORDERS VALUES(2,4,2,10,'2009-01-01 00:00:00:000')
INSERT INTO #ORDERS VALUES(3,1,1,11,'2009-01-01 00:00:00:000')
INSERT INTO #ORDERS VALUES(3,2,1,12,'2009-01-01 00:00:00:000')
INSERT INTO #ORDERS VALUES(3,3,1,13,'2009-01-01 00:00:00:000')
INSERT INTO #ORDERS VALUES(3,4,1,14,'2009-01-01 00:00:00:000')
INSERT INTO #ORDERS VALUES(3,5,1,15,'2009-01-01 00:00:00:000')

INSERT INTO #ORDERS VALUES(1,7,1,1, '2009-01-02 00:00:00:000')
INSERT INTO #ORDERS VALUES(1,8,1,2, '2009-01-02 00:00:00:000')
INSERT INTO #ORDERS VALUES(1,9,1,3, '2009-01-02 00:00:00:000')
INSERT INTO #ORDERS VALUES(1,10,1,4, '2009-01-02 00:00:00:000')
INSERT INTO #ORDERS VALUES(1,11,1,5, '2009-01-02 00:00:00:000')
INSERT INTO #ORDERS VALUES(1,12,1,6, '2009-01-02 00:00:00:000')
INSERT INTO #ORDERS VALUES(2,5,1,7, '2009-01-02 00:00:00:000')
INSERT INTO #ORDERS VALUES(2,6,1,8, '2009-01-02 00:00:00:000')
INSERT INTO #ORDERS VALUES(2,7,1,9, '2009-01-02 00:00:00:000')
INSERT INTO #ORDERS VALUES(2,8,1,10, '2009-01-02 00:00:00:000')
INSERT INTO #ORDERS VALUES(3,6,2,11, '2009-01-02 00:00:00:000')
INSERT INTO #ORDERS VALUES(3,7,2,12, '2009-01-02 00:00:00:000')
INSERT INTO #ORDERS VALUES(3,8,2,13, '2009-01-02 00:00:00:000')
INSERT INTO #ORDERS VALUES(3,9,2,14, '2009-01-02 00:00:00:000')
INSERT INTO #ORDERS VALUES(3,10,2,15,'2009-01-02 00:00:00:000')


INSERT INTO #SPECIAL_ORDERS VALUES(1,2,1, '2009-01-01 00:00:00:000')
INSERT INTO #SPECIAL_ORDERS VALUES(1,2,2, '2009-01-01 00:00:00:000')
INSERT INTO #SPECIAL_ORDERS VALUES(1,2,3, '2009-01-01 00:00:00:000')
INSERT INTO #SPECIAL_ORDERS VALUES(1,2,4, '2009-01-01 00:00:00:000')
INSERT INTO #SPECIAL_ORDERS VALUES(1,2,5, '2009-01-01 00:00:00:000')
INSERT INTO #SPECIAL_ORDERS VALUES(1,2,6, '2009-01-01 00:00:00:000')
INSERT INTO #SPECIAL_ORDERS VALUES(2,2,7, '2009-01-01 00:00:00:000')
INSERT INTO #SPECIAL_ORDERS VALUES(2,2,8, '2009-01-01 00:00:00:000')
INSERT INTO #SPECIAL_ORDERS VALUES(2,2,9, '2009-01-01 00:00:00:000')
INSERT INTO #SPECIAL_ORDERS VALUES(2,2,10,'2009-01-01 00:00:00:000')

INSERT INTO #SPECIAL_ORDERS VALUES(3,2,11, '2009-01-02 00:00:00:000')
INSERT INTO #SPECIAL_ORDERS VALUES(3,2,12, '2009-01-02 00:00:00:000')
INSERT INTO #SPECIAL_ORDERS VALUES(3,2,13, '2009-01-02 00:00:00:000')
INSERT INTO #SPECIAL_ORDERS VALUES(3,2,14, '2009-01-02 00:00:00:000')
INSERT INTO #SPECIAL_ORDERS VALUES(3,2,15, '2009-01-02 00:00:00:000')

Florian Reischl
Florian Reischl
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3745 Visits: 3934
Hello g8r

I'm not sure if I understood, but for the example result of your first post it might be just:


SELECT Visit_Date, COUNT(*)
FROM #Special_Orders
GROUP BY Visit_Date



Greetings
Flo


The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
stamic
stamic
SSC Veteran
SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)

Group: General Forum Members
Points: 279 Visits: 482
Thank you Flo. But I think that while that may show me how many items were ordered on each date, it will not show me how many customers in the special_orders table are there from their first visit, and how many are there from their second vist. Thank you for you reply.
Florian Reischl
Florian Reischl
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3745 Visits: 3934
Okay, next try.

If you want the count of visits of your customers within the special_orders use a aggregated sub-query. (In your sample data every customer/location has exactly two entries within the orders).


SELECT so.CUSTOMER_ID, so.LOCATION_ID, o.visit_count
FROM #SPECIAL_ORDERS so
JOIN (SELECT CUSTOMER_ID, LOCATION_ID, COUNT(*) visit_count
FROM #ORDERS
GROUP BY CUSTOMER_ID, LOCATION_ID) o
ON so.CUSTOMER_ID = o.CUSTOMER_ID AND so.LOCATION_ID = o.LOCATION_ID



Greets
Flo


The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
stamic
stamic
SSC Veteran
SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)

Group: General Forum Members
Points: 279 Visits: 482
Thank you, that is very close. Unfortunately what I am trying to get to is a summary of customers and items from the Special_Orders table in relation to which trip they made to the location. So I need to see, using this data, is that 10 customers in the special_orders table are in there from items ordered on their first visit and 5 are in there from items ordered on their second visit. In the sample data I created, the first 10 made another order at the location at a later date but are not in special_orders for the second visit but rather the first, nor are the other 5 in there from their first visit, but rather their second. I'm not really concerned about location as it was just a way to join, along with customer. So I need to see something like this:
Visit_number Customers
1 10
2 5

Thanks for helping me out!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search