user6559913 user6559913 - 2 months ago 8
MySQL Question

Joining multiple queries on a table and get a single select result

I have a table with columns Costcenter, Vendor, Tool , cost and quarter. I want to get result of cost difference between two quarters (say 15/16 Q3 and 15/16 Q2) as:

enter image description here

I have created query to get result for Costcenter and Vendor only initially as below but it is not joining two appropriately:
enter image description here

select a.Costcenter,c.Vendor,(SumA-SumB) as costcenter_diff, (SumC-SumD) as vendor_diff from
(select Costcenter ,sum(CostTotal) as SumA
from Rawdata
where Quarter='15/16 Q3'
group by Costcenter)as a,
(select Costcenter,sum(CostTotal) as SumB
from Rawdata
where Quarter='15/16 Q2'
group by Costcenter) as b
Join
(select Costcenter,Vendor,sum(CostTotal) as SumC
from Rawdata
where Quarter='15/16 Q3'
group by Costcenter,Vendor)as c,
(select Costcenter,Vendor,sum(CostTotal) as SumD
from Rawdata
where Quarter='15/16 Q2'
group by Costcenter,Vendor) as d where a.Costcenter = b.Costcenter and
c.Costcenter= d.Costcenter and c.Vendor= d.Vendor;

Answer

First, use conditional aggregation. For instance:

select CostCenter,
       sum(case when quarter = '15/16 Q2' then costtotal else 0 end) as sumq2,
       sum(case when quarter = '15/16 Q3' then costtotal else 0 end) as sumq3
from rawdata rd
where quarter in ('15/16 Q3', '15/16 Q2');

Then, do this for vendors and join them together:

select ccv.costcenter, ccv.vendor,
       (cc.sumq3 - cc.sumq2) as cc_diff,
       (ccv.sumq3 - ccv.sumq2) as ccv_diff
from (select CostCenter,
             sum(case when quarter = '15/16 Q2' then costtotal else 0 end) as sumq2,
             sum(case when quarter = '15/16 Q3' then costtotal else 0 end) as sumq3
      from rawdata rd
      where quarter in ('15/16 Q3', '15/16 Q2')
      group by CostCenter
     ) cc join
     (select CostCenter, vendor,
             sum(case when quarter = '15/16 Q2' then costtotal else 0 end) as sumq2,
             sum(case when quarter = '15/16 Q3' then costtotal else 0 end) as sumq3
      from rawdata rd
      where quarter in ('15/16 Q3', '15/16 Q2')
      group by CostCenter, vendor
     ) ccv
     on cc.costcenter = ccv.costcenter;

Notes:

  • Name columns something reasonable. a and b just don't make it easy to understand a query.
  • JOIN conditions should be in an ON clause not a WHERE clause.
  • Never use commas in the FROM clause. Always use explicit JOIN syntax.
Comments