RE: [firebird-support] Re: SubQuery Help
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]
[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
Re: [firebird-support] Re: SubQuery Help
Thanks everybody. I'll try it out.
Re: [firebird-support] Re: SubQuery Help
It did, but my fingers were quicker. With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! From: mailto:firebird-support@yahoogroups.com Sent: Tuesday, September 16, 2014 6:38 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] Re: SubQuery Help Yes it does. I posted a correction but apparently it never made it Dixon [Non-text portions of this message have been removed]
Re: [firebird-support] Re: SubQuery Help
Yes it does. I posted a correction but apparently it never made it Dixon Sent via the Samsung GALAXY S®4, an AT&T 4G LTE smartphone Original message From: "'Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]" Date:09/16/2014 12:16 PM (GMT-05:00) To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] Re: SubQuery Help I think this needs a GROUP BY a.Project_PK With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! From: mailto:firebird-support@yahoogroups.com Sent: Tuesday, September 16, 2014 6:12 PM To: firebird-support@yahoogroups.com Subject: [firebird-support] Re: SubQuery Help I'd recommend using a left join with table alias. Example 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 -- Dixon Epperson
Re: [firebird-support] Re: SubQuery Help
I think this needs a GROUP BY a.Project_PK With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! From: mailto:firebird-support@yahoogroups.com Sent: Tuesday, September 16, 2014 6:12 PM To: firebird-support@yahoogroups.com Subject: [firebird-support] Re: SubQuery Help I'd recommend using a left join with table alias. Example 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 -- Dixon Epperson
[firebird-support] Re: SubQuery Help
Sorry, forgot to add the group by statement so it should be like this: 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 -- Dixon Epperson
[firebird-support] Re: SubQuery Help
I'd recommend using a left join with table alias. Example 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 -- Dixon Epperson
