I have a table where where each project_id generates multiple rows

All we need is an easy explanation of the problem, so here it is.

My table below shows the rows and columns where the column data is filtered using a CASE syntax to differentiate capital from expense. The problem is I am getting multiple rows per project_ID.
I need one row per project_ID:
I have a table where where each project_id generates multiple rows

I need the output to be:

I have a table where where each project_id generates multiple rows
Here is my Oracle SQl (which includes many other columns not shown above):

SELECT
    pi.project_id,
    pi.project_projectnumber, 
    pi.project_projectname, 
    cst.cost1,

    CASE WHEN bi.code = '01-00-000' THEN  cst.ubr_ROM_Approved
         ELSE NULL
    END AS CAPITAL_ROM,  

    CASE WHEN bi.code = '02-00-000' 
         THEN cst.ubr_ROM_Approved
         ELSE NULL
    END AS EXPENSE_ROM,  

    CASE WHEN bi.code = '01-00-000' 
         THEN cst.ubacf_CD_Approved
         ELSE NULL
    END AS CAPITAL_ADJ_CD,  

    CASE WHEN bi.code = '01-00-000' 
         THEN cst.ubacf_SD_Approved
         ELSE NULL
    END AS CAPITAL_ADJ_SD,  

    CASE WHEN bi.code = '01-00-000' 
         THEN cst.ubacf_DD_Approved
         ELSE NULL
    END AS CAPITAL_ADJ_DD,  

    CASE WHEN bi.code = '01-00-000' 
         THEN cst.ubacf_Budget_Approved
         ELSE NULL
    END AS CAPITAL_ADJ_Budget,  

    CASE WHEN bi.code = '02-00-000' 
         THEN cst.ubacf_CD_Approved
         ELSE NULL
    END AS EXPENSE_ADJ_CD,  

    CASE WHEN bi.code = '02-00-000' 
         THEN cst.ubacf_SD_Approved
         ELSE NULL
    END AS EXPENSE_ADJ_SD,  

    CASE WHEN bi.code = '02-00-000' 
         THEN cst.ubacf_DD_Approved
         ELSE NULL
    END AS EXPENSE_ADJ_DD,  

    CASE WHEN bi.code = '02-00-000' 
         THEN cst.ubacf_Budget_Approved
         ELSE NULL
    END AS EXPENSE_ADJ_Budget


FROM turnerb_upi_view pi
INNER JOIN turnerb_sys_project_info_view pv ON (pv.pid = pi.project_id)
INNER JOIN turnerb_us_p1_view prj ON (prj.pid = pi.project_id)
INNER JOIN turnerb_budget_summary_viewb cst ON (cst.projectid = pi.project_id)
INNER JOIN turnerb_ubr_view rom ON (rom.project_id = pi.project_id)
INNER JOIN turnerb_ubacf_view badj ON (badj.project_id = pi.project_id)
INNER JOIN turnerb_budgetitem bi ON (bi.budgetid = cst.budgetid)

WHERE pi.project_status = '1' 
AND rom.status ='ROM_Approved' 
AND (badj.status = 'SD_Approved' 
     OR badj.status = 'CD_Approved' 
     OR badj.status = 'DD_Approved' 
     OR badj.status = 'Budget_Approved')

GROUP BY 
    pi.project_id,
    pi.project_projectnumber, 
    pi.project_projectname, 
    bi.code,
    cst.cost1,
    cst.ubr_ROM_Approved,
    cst.ubacf_CD_Approved,
    cst.ubacf_SD_Approved,
    cst.ubacf_DD_Approved,
    cst.ubacf_Budget_Approved

ORDER BY project_projectnumber;

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

to obtain the output you need, all that is necessary is

SELECT project_id, SUM(capital_rom), SUM (expense_rom), ...
   ...
   GROUP BY project_id

I do not see the connex between your desired output and your statement …

Method 2

The columns or expressions that you want to be unique in your output must be included in GROUP BY. The columns that need to be aggregated, or the columns that form expressions to be aggregated, must not be in GROUP BY.

Your query contains much more columns than the simplified example in your snapshots, and you are not elaborating on which ones are supposed to be unique in the resulting set. Therefore I am going to be guessing here. It seems to me the GROUP BY terms should be the first four columns in your SELECT clause:

pi.project_id,
pi.project_projectnumber, 
pi.project_projectname, 
cst.cost1,

You should keep those in GROUP BY and remove all the others. As for the SELECT clause, all the CASE expressions need to be encased in SUMs. Here are all the changes that you need to apply to your query:

SELECT
    pi.project_id,
    pi.project_projectnumber, 
    pi.project_projectname, 
    cst.cost1,

    SUM(  /* same expression, but aggregated */
        CASE WHEN bi.code = '01-00-000' THEN  cst.ubr_ROM_Approved
             ELSE NULL
        END
    ) AS CAPITAL_ROM,  

    SUM(  /* same expression, but aggregated */
        CASE WHEN bi.code = '02-00-000' 
             THEN cst.ubr_ROM_Approved
             ELSE NULL
        END
    ) AS EXPENSE_ROM,  

    ... /* follow the above pattern for the rest of the columns */

FROM ... /* no changes here */

WHERE ... /* no changes here */

GROUP BY
    /* only these four need to be GROUP BY terms */
    pi.project_id,
    pi.project_projectnumber, 
    pi.project_projectname, 
    cst.cost1

ORDER BY project_projectnumber;

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply