SQL query to find the count of number of same absences for an employee-CodePudding

I have a table:

PERSON_NUMBER       ABS_DATE            ABS_TYPE_NAME               ABS_DAYS
-----------------------------------------------------------------------------
1010            01-01-2022              PTO                             1
1010            01-01-2022              PTO                             1
1010            06-01-2022              PTO                             0.52
1020            02-02-2022              VACATION                        1
1020            02-02-2022              VACATION                        0.2
1030            01-12-2021              PTO                             1
1030            01-12-2021              PTO                             1
1040            02-12-2021              sick                            1                   
1040            30-12-2021              sick                            1
1050            30-01-2022              SICK                            1

I want to add another column to the output, COUNT that tells me instances where for one person there are repetive data with same ABS_TYPE on same date.

PERSON_NUMBER       ABS_DATE            ABS_TYPE_NAME               ABS_DAYS            COUNT
------------------------------------------------------------------------------
1010            01-01-2022              PTO                             1                2
1010            01-01-2022              PTO                             1                           
1010            06-01-2022              PTO                             0.52             1 
1020            02-02-2022              VACATION                        1                2       
1020            02-02-2022              VACATION                        1                
1030            01-12-2021              PTO                             1                2 
1030            01-12-2021              PTO                             1 
1040            02-12-2021              sick                            1                1                    
1040            30-12-2021              sick                            1                1 
1050            30-01-2022              SICK                            1                1

I am using -

COUNT(ABS_DATE) OVER (PARTITION BY ABS_DATE, PERSON_NUMBER, ABS_TYPE_NAME 
                      ORDER BY PERSON_NUMBER, ABS_TYPE_NAME)

But this is returning output 4 for the first row. Also it's returned for all rows. I want one value to come for these records.

Eg- if 2 came for 1st row, it should not come in 2nd

CodePudding user response：

When you are performing a COUNT with a partition clause, ORDER BY is not needed because it does not matter the order that the records are counted.

You can also use a second analytic function to determine the first row that will hold the "count" and only display that in the results.

Query

WITH
    absences (PERSON_NUMBER,
              ABS_DATE,
              ABS_TYPE_NAME,
              ABS_DAYS)
    AS
        (SELECT 1010, DATE '2022-01-01', 'PTO', 1 FROM DUAL
         UNION ALL
         SELECT 1010, DATE '2022-01-01', 'PTO', 1 FROM DUAL
         UNION ALL
         SELECT 1010, DATE '2022-01-06', 'PTO', 0.52 FROM DUAL
         UNION ALL
         SELECT 1020, DATE '2022-02-02', 'VACATION', 1 FROM DUAL
         UNION ALL
         SELECT 1020, DATE '2022-02-02', 'VACATION', 0.2 FROM DUAL
         UNION ALL
         SELECT 1030, DATE '2021-12-01', 'PTO', 1 FROM DUAL
         UNION ALL
         SELECT 1030, DATE '2021-12-01', 'PTO', 1 FROM DUAL
         UNION ALL
         SELECT 1040, DATE '2021-12-02', 'sick', 1 FROM DUAL
         UNION ALL
         SELECT 1040, DATE '2021-12-30', 'sick', 1 FROM DUAL
         UNION ALL
         SELECT 1050, DATE '2022-01-30', 'SICK', 1 FROM DUAL)
  SELECT person_number,
         abs_date,
         abs_type_name,
         abs_days,
         CASE rn WHEN 1 THEN cnt END     AS COUNT
    FROM (SELECT a.*,
                 COUNT (ABS_DATE) OVER (PARTITION BY ABS_DATE, PERSON_NUMBER, ABS_TYPE_NAME)
                     AS cnt,
                 ROW_NUMBER () OVER (PARTITION BY ABS_DATE, PERSON_NUMBER, ABS_TYPE_NAME ORDER BY 1)
                     AS rn
            FROM absences a)
ORDER BY person_number, abs_type_name, abs_date, rn;

Results

   PERSON_NUMBER     ABS_DATE    ABS_TYPE_NAME    ABS_DAYS    COUNT
________________ ____________ ________________ ___________ ________
            1010 01-JAN-22    PTO                        1        2
            1010 01-JAN-22    PTO                        1
            1010 06-JAN-22    PTO                     0.52        1
            1020 02-FEB-22    VACATION                   1        2
            1020 02-FEB-22    VACATION                 0.2
            1030 01-DEC-21    PTO                        1        2
            1030 01-DEC-21    PTO                        1
            1040 02-DEC-21    sick                       1        1
            1040 30-DEC-21    sick                       1        1
            1050 30-JAN-22    SICK                       1        1