Calculating weighted sum and sum of columns for report query SQL

Question

I have a excel sheet in which there is a sample data that I need to show on report.

As it can be seen that the highlighted 10% is calculating using the formula C11/C21. For the first record C11 = 3900 and C21 = 37250 (Sum of all PO Amount column). So (3900/37250) * 100 = 0.104697 * 100 = 10.4697 or 10%

Similarly Total Weighted is the sum of all Weighted Value column Values.

Now I made the sample data first.

CREATE TABLE sample_po_data ("PO_NUMBER", "PO_AMOUNT", "TASK_NAME") AS
  SELECT 4567, 3900, 'Plumbing Rough' FROM DUAL
UNION ALL SELECT 5678, 8000, 'Pour Floor' FROM DUAL
UNION ALL SELECT 6789, 15000, 'Framing' FROM DUAL
UNION ALL SELECT 8945, 2500, 'Windows' FROM DUAL
UNION ALL SELECT 5432, 3500, 'Electric' FROM DUAL
UNION ALL SELECT 4321, 450, 'LowVolt' FROM DUAL
UNION ALL SELECT 1234, 3900, 'HVAC Rough' FROM DUAL;

Then I used the following query

select 
    PO_NUMBER AS "PO #",
    Round((PO_AMOUNT / SUM(PO_AMOUNT) OVER ())*100) || '%' as "Weighted Value",
    PO_AMOUNT AS "PO Amount", 
    Round((PO_AMOUNT / SUM(PO_AMOUNT) OVER ())*100) || '%' as "Total Percent Complete",
    SUM(PO_AMOUNT) OVER () AS "Total Committed And Budgeted"
    -- SUM(Round((PO_AMOUNT / SUM(PO_AMOUNT) OVER ())*100)) OVER () || '%' as "Total Weighted"  -- Total weighted   (Sum of all Weighted Value Column)
    -- % Completed      (Sum of all Total Percent Complete Column)
from sample_po_data;

The above query gives the following result

Now how can I sum the Weighted Value and Total Percent Complete columns ? Both are same.

Also I want to ask the above query is right or there is any better way to write the above query. Actually I am using SUM(PO_AMOUNT) in the select three times. Also my two columns are just same.

Total Committed And Budgeted column is appearing with all the records. Although I need it just once. Same is true for Total weighted column and % Completed column.

How can I make this query better ?

Thanks

Answer 1

Use SUM as an analytic function and conditional aggregation:

select 
    PO_NUMBER AS "PO #",
    Round(PO_AMOUNT / SUM(PO_AMOUNT) OVER () * 100) AS "Weighted Value",
    PO_AMOUNT AS "PO Amount",
    Completion_Date,
    Round(
      CASE WHEN completion_date IS NULL THEN 0 ELSE PO_AMOUNT END
      / SUM(PO_AMOUNT) OVER () * 100
    ) AS "Percent Complete",
    SUM(PO_AMOUNT) OVER () AS "Total Committed And Budgeted",
    -- This will always add up to 100%; you do not need to calculate it.
    100 as "Total Weighted",
    Round(
      SUM(CASE WHEN completion_date IS NULL THEN 0 ELSE PO_AMOUNT END) OVER ()
      / SUM(PO_AMOUNT) OVER () * 100
    ) AS "Total Percent Complete"
from sample_po_data;

Which, for the sample data (with a COMPLETION_DATE column added):

CREATE TABLE sample_po_data (PO_NUMBER, PO_AMOUNT, COMPLETION_DATE, TASK_NAME) AS
          SELECT 4567,  3900, DATE '2021-01-01', 'Plumbing Rough' FROM DUAL
UNION ALL SELECT 5678,  8000, DATE '2021-02-01', 'Pour Floor' FROM DUAL
UNION ALL SELECT 6789, 15000, DATE '2021-03-01', 'Framing' FROM DUAL
UNION ALL SELECT 8945,  2500, NULL,              'Windows' FROM DUAL
UNION ALL SELECT 5432,  3500, NULL,              'Electric' FROM DUAL
UNION ALL SELECT 4321,   450, NULL,              'LowVolt' FROM DUAL
UNION ALL SELECT 1234,  3900, NULL,              'HVAC Rough' FROM DUAL;

Outputs:

PO #	Weighted Value	PO Amount	COMPLETION_DATE	Percent Complete	Total Committed And Budgeted	Total Weighted	Total Percent Complete
4567	10	3900	01-JAN-21	10	37250	100	72
5678	21	8000	01-FEB-21	21	37250	100	72
6789	40	15000	01-MAR-21	40	37250	100	72
8945	7	2500		0	37250	100	72
5432	9	3500		0	37250	100	72
4321	1	450		0	37250	100	72
1234	10	3900		0	37250	100	72

---

For your full query:

SELECT poh.po_full_number AS "PO #", 
       100 * poh.po_total_amount / SUM(poh.po_total_amount) OVER ()
         AS "Weighted Value",
       poh.po_total_amount AS "PO Amount", 
       poh.approve_date AS "PO Approved Date", 
       st.task_name AS "Task Name", 
       st.start_date AS "Task Projected Start Date",
       st.original_finish_date AS "Actual End Date",
       SUM(poh.po_total_amount) OVER () AS "Total Committed And Budgeted",
       100 AS "Total weighted"
       SUM(
         CASE WHEN st.end_date IS NOT NULL THEN poh.total_amount ELSE 0 END
       ) OVER () / SUM(poh.po_total_amount) OVER () * 100
         AS "% Completed"
FROM   po_header poh
       inner join unit u on poh.unit_id = u.unit_id 
       inner join company_project cp on poh.project_id = cp.project_id 
       inner join company_project_setting cps on cp.project_id = cps.project_id
       inner join po_header_status_lk pohslk on poh.po_status = pohslk.code 
       LEFT JOIN schedule_task st on poh.schedule_task_id = st.schedule_task_id 
WHERE  poh.project_id = 511                           
AND    poh.po_type = 100 
AND    poh.po_status = 101                                                 
AND    u.status_id in (102, 103)                                       
AND    (  (cps.construction_type = 1 AND st.is_finished = 1)
       OR (cps.construction_type in (0, 2) AND st.is_finished != 1))            
order by
       poh.po_header_id desc;