Lina Lina - 2 months ago 13
SQL Question

can we use two sum fuction in same query in Postgres

I written a query as mentioned below :

select
sum(co.BOMParentQty) as hiredqty,
cy.dehireqty as dehire,
cy.M_product_id,
co.Project_Prod
from c_order co
inner join M_CycleDays cy ON (co.Project_Prod :: numeric = cy.M_product_id)
where co.c_doctype_id = 1000600
AND co.Project_Prod = '1000470'
group by cy.M_product_id, co.Project_Prod, dehire


it gives me output for two lines but i want only single line with the sum of dehireqty if i put sum function for dehire qty column then it sums two times
Actual Output without Sum of dehireqty column is (two records):

6.00;2.00;1000470;"1000470"
6.00;3.00;1000470;"1000470"


After put sum function for dehireqty column:

12.00;15.00;1000470;"1000470"


But actually I want:

6.00;5.00;1000470;"1000470"


How can I get this result?

Answer

Just make your query a sub-query and sum-up dehireqty in outer-query:

SELECT
    hiredqty,
    SUM(dehire) AS dehireqty,
    M_product_id,
    Project_Prod
FROM (
    select 
      sum(co.BOMParentQty) as hiredqty, 
      cy.dehireqty as dehire, 
      cy.M_product_id,
      co.Project_Prod 
    from c_order co 
      inner join M_CycleDays cy ON (co.Project_Prod :: numeric = cy.M_product_id) 
    where co.c_doctype_id = 1000600  
      AND co.Project_Prod = '1000470' 
    group by cy.M_product_id, co.Project_Prod, dehire
) AS subquery Group by hiredqty,M_product_id,Project_Prod;