Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Order number grouping and count Expand / Collapse
Author
Message
Posted Wednesday, March 4, 2009 1:34 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 2:38 PM
Points: 255, Visits: 467
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!
Post #668643
Posted Wednesday, March 4, 2009 1:37 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:34 AM
Points: 6,259, Visits: 2,031
Can you post sample data and DDL of your tables ;)



* Noel
Post #668652
Posted Wednesday, March 4, 2009 2:26 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 2:38 PM
Points: 255, Visits: 467
-- 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')
Post #668705
Posted Wednesday, March 4, 2009 2:54 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 1,893, Visits: 3,932
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
Post #668724
Posted Wednesday, March 4, 2009 3:50 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 2:38 PM
Points: 255, Visits: 467
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.
Post #668773
Posted Wednesday, March 4, 2009 4:05 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 1,893, Visits: 3,932
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
Post #668783
Posted Wednesday, March 4, 2009 6:50 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 2:38 PM
Points: 255, Visits: 467
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!
Post #668843
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse