Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Aggregating rows based on conditions

Jason SmithFeb 9 2024 — edited Feb 9 2024

Given this result set:

WITH x AS (SELECT 0 id, 0 monday_hours, 4 tuesday_hours, 'Standard Labor' numonic FROM dual
            UNION ALL
           SELECT 1, 0, 2, 'Administration' FROM dual
            UNION ALL
           SELECT 2, 1, 0, 'Administration' FROM dual)
SELECT * FROM x;

        ID MONDAY_HOURS TUESDAY_HOURS NUMONIC       
---------- ------------ ------------- --------------
         0            0             4 Standard Labor
         1            0             2 Administration
         2            1             0 Administration

I need to aggregate the rows with ID's 1 and 2 because both have the same NUMONIC values and between the IDs 1 and 2 the MONDAY_HOURS and TUESDAY_HOURS have a zero on one of the rows. I feel like there should be an easy way to do this, but it is eluding me. The result should look like the below. Any thoughts?

        ID MONDAY_HOURS TUESDAY_HOURS NUMONIC       
---------- ------------ ------------- --------------
         0            0             4 Standard Labor
         1            1             2 Administration
This post has been answered by User_3ABCE on Feb 9 2024
Jump to Answer
Comments
Post Details
Added on Feb 9 2024
2 comments
79 views