CODEWITHSUNDEEP
sqlpostgresql
Zaki Zakaria
Zaki Zakaria

Reputation: 137

How to count repeating values in a column in PostgreSQL?

Hi I have a table like below, and I want to count the repeating values in the status column. I don't want to calculate the overall duplicate values. For example, I just want to count how many "Offline" appears until the value changes to "Idle".

enter image description here

This is the result I wanted. Thank you.

enter image description here

Upvotes: 2

Views: 1690

Answers (2)

Akina
Akina

Reputation: 42728

WITH 
cte1 AS ( SELECT status, 
                 "date", 
                 workstation, 
                 CASE WHEN status = LAG(status) OVER (PARTITION BY workstation ORDER BY "date")
                      THEN 0
                      ELSE 1 END changed
          FROM test ),
cte2 AS ( SELECT status, 
                 "date", 
                 workstation, 
                 SUM(changed) OVER (PARTITION BY workstation ORDER BY "date") group_num 
          FROM cte1 )
SELECT status, COUNT(*) "count", workstation, MIN("date") "from", MAX("date") "till"
FROM cte2
GROUP BY group_num, status, workstation;

fiddle

Upvotes: 0

Vladimir Baranov
Vladimir Baranov

Reputation: 32693

This is often called gaps-and-islands.

One way to do it is with two sequences of row numbers.

Examine each intermediate result of the query to understand how it works.

WITH
CTE_rn
AS
(
    SELECT
        status
        ,dt
        ,ROW_NUMBER() OVER (ORDER BY dt) as rn1
        ,ROW_NUMBER() OVER (PARTITION BY status ORDER BY dt) as rn2
    FROM
        T
)
SELECT
    status
    ,COUNT(*) AS cnt
FROM
    CTE_rn
GROUP BY
    status
    ,rn1-rn2
ORDER BY
    min(dt)
;

Result

| status  | cnt |
|---------|-----|
| offline | 2   |
| idle    | 1   |
| offline | 2   |
| idle    | 1   |

Upvotes: 2

Related Questions