CODEWITHSUNDEEP
mysqlsql
user3169268
user3169268

Reputation: 13

SQL aggregating running count of records

I am relatively new to SQL so apologies for any stupid questions, but I can't even get close on this.

I have a data set of customer orders which consists of Cust_ID and Date. I want to return a query that has all the customer orders adding two fields, "Date of first order" and "order count"

Cust_ID    Date        FirstOrder     orderCount
5001       04/10/13    04/10/13       1
5001       11/10/13    04/10/13       2
5002       11/10/13    11/10/13       1
5001       17/10/13    04/10/13       3
5001       24/10/13    04/10/13       4
5002       24/10/13    11/10/13       2

Any pointers would be much appreciated.

Thanks

Upvotes: 1

Views: 79

Answers (3)

suf.agent
suf.agent

Reputation: 136

If I understood you correctly:

Source data you have:

Cust_ID    Date
5001       04/10/13   
5001       11/10/13   
5002       11/10/13   
5001       17/10/13  
5001       24/10/13    
5002       24/10/13

Result dataset you expect:

Cust_ID    Date        FirstOrder     OrderNumber
5001       04/10/13    04/10/13       1
5001       11/10/13    04/10/13       2
5002       11/10/13    11/10/13       1
5001       17/10/13    04/10/13       3
5001       24/10/13    04/10/13       4
5002       24/10/13    11/10/13       2

Then query should be (if using AF):

SELECT Cust_ID, Date,
       MIN(Date) over ( partition by Cust_ID ) as FirstOrder,
       RowNumber() over ( partition by Cust_ID order by Date asc ) as OrderNumber
FROM   Orders

Excluding AF, using only standart SQL:

SELECT S.Cust_ID, S.Date, MIN(J.Date) as FirstDate, Count(S.Cust_id)
FROM Orders S
INNER JOIN Orders J
ON S.Cust_ID = J.Cust_ID and S.Date >= J.Date
GROUP BY S.Cust_id, S.Date

Upvotes: 0

Jason Heo
Jason Heo

Reputation: 10236

Could you try this?

SELECT tab.Cust_ID, tab.Date, x.FirstOrder, x.orderCount
FROM tab INNER JOIN (
    SELECT Cust_ID, MIN(Date) AS FirstOrder,
        COUNT(*) AS orderCount
    FROM tab
    GROUP BY CustID
) x ON tab.Cust_ID = x.Cust_ID

The inner query selects FirstOrder and orderCount for each customer and finally joined with for each orders.

updated

with user variables you can keep track of sequence of order count of Cust_ID

SELECT *
FROM (
    SELECT tab.Cust_ID, tab.Date AS Date, x.FirstOrder, x.orderCount,
        IF(@prev_cust_id = tab.Cust_ID, @cnt := @cnt + 1, @cnt := 1)
    FROM tab INNER JOIN (
        SELECT Cust_ID, MIN(Date) AS FirstOrder,
            COUNT(*) AS orderCount
        FROM tab
        GROUP BY CustID
    ) x ON tab.Cust_ID = x.Cust_ID,
    (SELECT @prev_cust_id := 0, @cnt := 1) y
    ORDER BY tab.Cust_ID
) x
ORDER BY Date;

Upvotes: 0

AgRizzo
AgRizzo

Reputation: 5271

SELECT foo.Cust_ID
     , foo.`Date`
     , MIN(p.`Date`) AS FirstOrder
     , COUNT(*) AS orderCount
FROM foo
JOIN foo AS p
  ON p.Cust_id = foo.Cust_id
    AND p.`Date` <= foo.`Date`
GROUP BY foo.Cust_ID, foo.`Date`
ORDER BY foo.`Date`;

Upvotes: 1

Related Questions