CODEWITHSUNDEEP
sqloracle-database
RDG
RDG

Reputation: 183

SQL [Oracle]: Count number of previous records with same client ID

Had a brief search online to see if an answer to this already existed. Couldn't find anything that tackled this particular situation (which was a surprise, as I would have assumed it was fairly common).

Basically I have a table that records registrations with a service, along with the ID of the client registering, and the start and end dates of the registration. See below for an example of the table structure:

Reg ID | Client ID | Reg Start  | Reg End

R6       C1          01-06-2016   Null
R5       C2          20-05-2016   02-06-2016
R4       C2          14-03-2016   11-05-2016
R3       C1          10-03-2016   05-05-2016
R2       C2          28-01-2016   02-02-2016
R1       C2          10-11-2015   23-12-2015

What I want to do, is pull the records from the table but also add a column that returns a count of how many previous registrations are in the table with the same client ID for each record. For the sake of clarity, I only want to count registrations for each record that have the same client ID and have a start date less than that of the record.

As such, a successful query would return the following output:

Reg ID | Client ID | Reg Start  | Reg End    | # previous reg

R6       C1          01-06-2016   Null         1
R5       C2          20-05-2016   02-06-2016   3
R4       C2          14-03-2016   11-05-2016   2
R3       C1          10-03-2016   05-05-2016   0
R2       C2          28-01-2016   02-02-2016   1
R1       C2          10-11-2015   23-12-2015   0

Has anyone had previous experience of doing this who could offer a viable solution?

My initial thought process was to create a sub query that would count registrations grouped by client ID, and then join this to my main query using client ID. For example:

SELECT
t1.reg_id
t1.client_id
t1.reg_start
t1.reg_end
FROM registrations t1
JOIN
  (SELECT
   client_id
   count(reg_id)
   FROM registrations
   GROUP BY client_id) t2
ON t1.client_id = t2.client_id

However, this would return a count of the total registrations for a given client per record, where as I specifically require the previous registrations.

Any input that the hive mind could provide would be much appreciated. I think I've hit a bit of a wall with my currently quite limited SQL knowledge here :/

Also I should probably mention that I'm using an Oracle database!

Upvotes: 2

Views: 1437

Answers (4)

Carlo
Carlo

Reputation: 1579

The easiest way, in my opinion, is to build a subquery in the SELECT statement of main query, as follow:

SELECT 
    t1.*, 
    (
        SELECT COUNT(*) 
        FROM registrations t2 
        WHERE t2.client_id = t1.client_id
          AND t2.reg_start < t1.reg_start
    ) AS previous_reg
FROM registrations t1
ORDER BY reg_id DESC

Upvotes: 0

user330315
user330315

Reputation:

This can be done with a window function and a running sum:

select reg_id, client_id, reg_start, reg_end,
       count(*) over (partition by client_id order by reg_start) - 1 as previous_reg
from registrations
order by reg_start desc;

Upvotes: 2

Giorgos Betsos
Giorgos Betsos

Reputation: 72185

In SQL Server you can use the following query:

SELECT [Reg ID], [Client ID], Reg_Start, Reg_End,
       COUNT(*) OVER (PARTITION BY [Client ID] ORDER BY Reg_Start) - 1
FROM mytable

COUNT, when applied with an ORDER BY clause, returns the running total of records count. If this is available in Oracle, then it should return the value you want.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270653

You can do this with row_number():

select r.*,
       (row_number() over (partition by client_id order by reg_start) - 1) as cnt
from registrations r;

Upvotes: 2

Related Questions