1

I'm fairly close to this solution, but I just need a little help getting over the end.

I'm trying to get a running count of the occurrences of client_ids regardless of the date, however I need the dates and ids to still appear in my results to verify everything.

I found part of the solution here but have not been able to modify it enough for my needs.

Here is what the answer should be, counting if the occurrences of the client_ids sequentially :

id  client_id   deliver_on  running_total
1   138         2017-10-01  1
2   29          2017-10-01  1
3   138         2017-10-01  2
4   29          2013-10-02  2
5   29          2013-10-02  3
6   29          2013-10-03  4
7   138         2013-10-03  3

However, here is what I'm getting:

id  client_id   deliver_on  running_total
1   138         2017-10-01  1
2   29          2017-10-01  1
3   138         2017-10-01  1
4   29          2013-10-02  3
5   29          2013-10-02  3
6   29          2013-10-03  1
7   138         2013-10-03  2

Rather than counting the times the client_id appears sequentially, the code counts the time the id appears in the previous date range.

Here is my code and any help would be greatly appreciated.

Thank you,

SELECT n.id, n.client_id, n.deliver_on, COUNT(n.client_id) AS "running_total"

FROM orders n 

LEFT JOIN orders o 
    ON (o.client_id = n.client_id 
            AND n.deliver_on > o.deliver_on)          

GROUP BY n.id, n.deliver_on, n.client_id
ORDER BY n.deliver_on ASC

* EDIT WITH ANSWER *

I ending up solving my own question. Here is the solution with comments:

-- Set "1" for counting to be used later

WITH DATA AS (

SELECT

   orders.id, 
   orders.client_id, 
   orders.deliver_on,
   COUNT(1) -- Creates a column of "1" for counting the occurrences

   FROM orders

   GROUP BY 1

   ORDER BY deliver_on, client_id

)

SELECT

   id,
   client_id,
   deliver_on,
   SUM(COUNT) OVER (PARTITION BY client_id 
                           ORDER BY client_id, deliver_on 
                           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) -- Counts the sequential client_ids based on the number of times they appear

 FROM DATA

1 Answer 1

1

Just the answer posted to close the question:

-- Set "1" for counting to be used later
WITH DATA AS (

SELECT

   orders.id, 
   orders.client_id, 
   orders.deliver_on,
   COUNT(1) -- Creates a column of "1" for counting the occurrences

   FROM orders

   GROUP BY 1

   ORDER BY deliver_on, client_id

)

SELECT

   id,
   client_id,
   deliver_on,
   SUM(COUNT) OVER (PARTITION BY client_id 
                           ORDER BY client_id, deliver_on 
                           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) -- Counts the sequential client_ids based on the number of times they appear

 FROM DATA
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.