Query returning result in multiple rows for same employee

Question

I have a table with multiple values of elements for an element with corresponding input values:

EE   ELEMENT_NAME     RESULT_VALUE   EFFECTIVE DATE   INPUT VALUE
-----------------------------------------------------------------
12   Overtime           1000           10-APR-2023      earning
12   Overtime           10             10-APR-2023      Hours
12   REGULAR_RETRO      110            10-APR-2023      earning
11   REGULAR_RETRO      120            10-apr-2023      earning

The required output is:

EE   REGULAR_RETRO   OVERTIME_PAID          OVERTIME_Hours_taken
----------------------------------------------------------------
12   110                1000                        10  
11   120

This query is returning two rows:

SELECT
    ee person_number,
    SUM(CASE 
            WHEN peen.element_name IN 'REGULAR RETRO'
                THEN (RESULT_VALUE) 
        END) REGULAR_RETRO,
    SUM(CASE 
            WHEN peen.element_name IN 'OVERTIME' AND input_value = 'Hours'
                THEN (RESULT_VALUE)
        END) OVERTIME_hours,
    SUM(CASE 
            WHEN peen.element_name IN 'OVERTIME' AND input_value =  'earning'
                THEN (RESULT_VALUE) 
        END) OVERTIME_paid 
FROM
    PER_ALL_PEOPLE_F PAPF
    PER_ELEMENT_NAME PEEN,
    PEr_ELEMENT_TYPE PET
WHERE
    PAPF.PERSON_ID = PEEN.PERSON_ID
    AND PEEN.ELEMENT_ID = PET.ELEMENT_ID
    AND PET.INPUT_VALUE IN ('Earning','Hours')
    AND peen.element_name IN ('REGULAR RETRO', 'OVERTIME')
GROUP BY
    person_number

But this query is returning two rows for ee#12 since he has both input name - earning and hours but it should come in 1 row -

EE   REGULAR_RETRO   OVERTIME_PAID          OVERTIME_Hours_taken
-----------------------------------------------------------------
12   110                1000                        0 
12   110                 0                          10  
11   120

[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (more than **30 years!!** ago) and its use is discouraged — marc_s, May 09 '23 at 04:07
You don't need to group by the `element_name`, group by `person_number` only. The case expression inside the aggregate function will take care of filtering out the unwanted `element_name`s for each group. — ahmed, May 09 '23 at 05:37
Also, why you are using `Then SUM(RESULT_VALUE)` , it should be `Then RESULT_VALUE`. — ahmed, May 09 '23 at 05:49
When you do a `GROUP BY person_number`, you should only get 1 line for person_number=12. — Luuk, May 09 '23 at 13:25
Please share info from the 3 tables (`PER_ALL_PEOPLE_F`, `PER_ELEMENT_NAME`, `PEr_ELEMENT_TYPE`) with data from those 3 tables, which reproduced this behavior. If needed you can create a [DBFIDDLE Oracle](https://dbfiddle.uk/7Jxju0AY) — Luuk, May 09 '23 at 13:28
Requesting more info, to see if this question is still open (and valid) after last edited on May 9 at 13:15 ... (The given [answer](https://stackoverflow.com/a/76247879/724039) is a repetition of the question above...) — Luuk, May 14 '23 at 14:18

score 0 · Accepted Answer · answered May 14 '23 at 14:05

If you have your data prepared as in the question - you could pivot rows into ccolumns, use case expressions to manage the data and aggregate grouped by EE to get the expected result:

WITH          --  Sample Data
    tbl (EE, ELEMENT_NAME, RESULT_VALUE, EFFECTIVE_DATE, INPUT_VALUE) AS
        (   Select 12, 'Overtime', 1000, To_Date('10-APR-2023', 'dd-MON-yyyy'), 'earning' From Dual Union All
            Select 12, 'Overtime',   10, To_Date('10-APR-2023', 'dd-MON-yyyy'), 'Hours' From Dual Union All
            Select 12, 'REGULAR_RETRO',  110, To_Date('10-APR-2023', 'dd-MON-yyyy'), 'earning' From Dual Union All
            Select 11, 'REGULAR_RETRO',  120, To_Date('10-APR-2023', 'dd-MON-yyyy'), 'earning' From Dual 
        )
--
--  M a i n   S Q L .
SELECT  EE, 
        Max(REGULAR_RETRO) "REGULAR_RETRO", 
        Max(OVERTIME_PAID) "OVERTIME_PAID",
        Max(OVERTIME_HOURS_TAKEN) "OVERTIME_HOURS_TAKEN"
FROM    (   Select  EE, 
                    Case When INPUT_VALUE = 'earning' THEN REGULAR_RETRO_PAID End "REGULAR_RETRO", 
                    Case When INPUT_VALUE = 'earning' THEN OVERTIME_PAID End "OVERTIME_PAID",
                    Case When INPUT_VALUE = 'Hours' THEN OVERTIME_HOURS_TAKEN End "OVERTIME_HOURS_TAKEN"
            From    tbl
            PIVOT   (   Sum(RESULT_VALUE) "PAID", Sum(RESULT_VALUE) "HOURS_TAKEN"
                  For ELEMENT_NAME IN('REGULAR_RETRO' "REGULAR_RETRO", 'Overtime' "OVERTIME" )
                )
        )
GROUP BY EE
ORDER BY EE Desc
--
--  R e s u l t :
--          EE REGULAR_RETRO OVERTIME_PAID OVERTIME_HOURS_TAKEN
--  ---------- ------------- ------------- --------------------
--          12           110          1000                   10 
--          11           120

Query returning result in multiple rows for same employee

1 Answers1