| Subject | Re: SubQuery Help |
|---|---|
| Author | |
| Post date | 2014-09-16T18:45:26Z |
Unfortunately, this gives the wrong result if there are multiple costs or quantities for any project. Assume the following (all for the same project)>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
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
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