CODEWITHSUNDEEP
sqlsql-servergaps-and-islands
winner_joiner
winner_joiner

Reputation: 14695

Count rows in table that are the same in a sequence

I have a table that looks like this

 +----+------------+------+
 | ID | Session_ID | Type |
 +----+------------+------+
 |  1 |          1 |    2 |
 |  2 |          1 |    4 |
 |  3 |          1 |    2 |
 |  4 |          2 |    2 |
 |  5 |          2 |    2 |
 |  6 |          3 |    2 |
 |  7 |          3 |    1 |
 +----+------------+------+

And I would like to count all occurences of a type that are in a sequence.

Output look some how like this:

 +------------+------+-----+
 | Session_ID | Type | cnt |
 +------------+------+-----+
 |          1 |    2 |   1 |
 |          1 |    4 |   1 |
 |          1 |    2 |   1 |
 |          2 |    2 |   2 |
 |          3 |    2 |   1 |
 |          3 |    1 |   1 |
 +------------+------+-----+

A simple group by like

SELECT session_id, type, COUNT(type)
FROM table
GROUP BY session_id, type

doesn't work, since I need to group only rows that are "touching".

Is this possible with a merge sql-select or will I need some sort of coding. Stored Procedure or Application side coding?

UPDATE Sequence:
If the following row has the same type, it should be counted (ordered by ID).

to determine the sequence the ID is the key with the session_ID, since I just want to group rows with the same session_ID.

So if there are 3 rows is in one session

Input:

 +----+------------+------+
 | ID | Session_ID | Type |
 +----+------------+------+
 |  1 |          1 |    1 |
 |  2 |          1 |    1 |
 |  3 |          1 |    2 |
 +----+------------+------+

The squence is Row 1 to Row 2. This three row should output

Output:

 +------------+------+-------+
 | Session_ID | Type | count |
 +------------+------+-------+
 |          1 |    1 |     2 |
 |          3 |    2 |     1 |
 +------------+------+-------+

Upvotes: 0

Views: 377

Answers (1)

Shikhar Arora
Shikhar Arora

Reputation: 886

You can use a difference of id and row_number() to identify the gaps and then perform your count

;with cte as
(
Select *, id - row_number() over (partition by session_id,type order by id) as grp 
from table
)
select session_id,type,count(*) as cnt
from cte
group by session_id,type,grp 
order by max(id)

Upvotes: 2

Related Questions