The question:
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 need the output to be:
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;
The Solutions:
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
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;
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