select

a.Project_PK,

PC.b_cost AS Sum_of_Cost,

PC.b_estimate_cost AS Sum_of_Estimated_Cost,

PQ.c_qty AS Sum_of_Quantity,

PQ.c_estimate_quantity AS Sum_of_Estimated_Quantity



FROM Projects a



LEFT JOIN

(

select

b.project_pk,

sum(b.cost) as b_cost,

sum(b.estimate_cost) as b_estimate_cost

from cost b

group by b.project_pk,

) ProjectCost PC ON PC. project_pk = a.project_pk



LEFT JOIN

(

select

c.project_pk,

sum(c.qty) as c_qty,

sum(c.estimate_qty) as c_estimate_qty

from Quantity c

group by c.project_pk,

) ProjectQty PQ ON PQ.project_pk = a.project_pk







From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Sent: 16 September 2014 08:45 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: SubQuery Help

>Select
> a.Project_PK

>, SUM(b.cost) AS Sum_of_Cost

>, SUM(b.estimate_cost) AS Sum_of_Estimated_Cost

>, SUM(c.quantity) AS Sum_of_Quantity

>, SUM(c.estimate_quantity) AS Sum_of_Estimated_Quantity

>FROM Projects a

>LEFT JOIN Cost b ON a.project_pk = b.project_pk

>LEFT JOIN Quantity c ON a.project_pk = c.project_pk

>GROUP BY a.Project_PK

Unfortunately, this gives the wrong result if there are multiple costs or quantities for any project. Assume the following (all for the same project)



COST

1

2



Quantity

1

2

3



The sums that ought to be given is 3 and 6, whereas I'm pretty certain the above statement would yield 9 and 12.



Rather than this solution, you should either use one or two CTEs or a corrected version of the original solution.



So either something like



with SumCost(Project_PK, Sum_of_Cost, Sum_of_Estimated_Cost) as

(select p.Project_PK, sum(c.cost), sum(c.estimate_cost)

from Projects p

join Costs c on p.project_pk=c.project_pk

group by 1)



select p.Project_PK, c.Sum_of_Cost, c.Sum_of_Estimated_Cost,

SUM(q.quantity) AS Sum_of_Quantity,

SUM(q.estimate_quantity) AS Sum_of_Estimated_Quantity

from Projects p

left join SumCost c on p.project_pk=c.project_pk

left join Quantity q on p.project_pk=q.project_pk

group by 1, 2, 3



or



Select
p.Project_PK,
(select sum(c.cost) from Costs c where p.project_pk=c.project_pk) as Sum_of_Cost,
(select sum(c.estimate_cost)from Costs c where p.project_pk=c.project_pk) as Sum_of_Estimated_Cost,
(select sum(q.quantity) from Quanties q where p.project_pk=q.project_pk) as Sum_of_Quantity,
(select sum(q.estimate_Quantity) from Quantity q where p.project_pk=q.project_pk) as Sum_of_Estimated_Quantity
from Projects p



Looking at the alternatives, I actually think the latter (i.e. the original and not the CTE), seems to be the simplest solution.



HTH,

Set





[Non-text portions of this message have been removed]