SQL - join multiple Select statements

Question

I am trying to combine four different select statements into one that gives me the desired output.

Two of the statements can be seen below (they and the others are identical with the exception of Y.Date_Year)

select sum(N.EBIT)/Sum(S.Sales), M.Date_Month from EBIT as N 
inner join Date_Year as Y on Y.Date_Year_Id = N.Date_Year_Id
inner join Sales As S on S.Company_Id = N.Company_Id
inner join Date_Month As M on M.Date_Month_Id=N.Date_Month_Id
where Y.Date_Year = 2014 and (N.Date_Month_Id = S.Date_Month_Id And N.Date_Year_Id = S.Date_Year_Id) and N.EBIT <> 0 and S.Sales <> 0
group by M.Date_Month

select sum(N.EBIT)/Sum(S.Sales), M.Date_Month from EBIT as N
inner join Date_Year as Y on Y.Date_Year_Id = N.Date_Year_Id
inner join Sales As S on S.Company_Id = N.Company_Id
inner join Date_Month As M on M.Date_Month_Id=N.Date_Month_Id
where Y.Date_Year = 2015 and (N.Date_Month_Id = S.Date_Month_Id And N.Date_Year_Id = S.Date_Year_Id) and N.EBIT <> 0 and S.Sales <> 0
group by M.Date_Month

They give me different views with the Date_Month Column and the EBIT/Sales Column. As of now I have to go to excel, paste the different values in and arrange them so they go from starting date (First month in Date_Month Column) to the ending date (Last month in Date_Month Column) and then move the different EBIT/Sales values in position.

The first Statement has Data from 2012-01-31 to 2015-11-30 while the second statement has Data from 2012-01-31 to 2016-11-30. I would like to have a table that looks somewhat like this below:

Date_Month       EBIT/Sales 2014         EBIT/Sales 2015      
2012-01-31       0.09                     0.10
....             .....                    .....
2016-11-30       'Null'                   0.098

Hence that they are in the same list but wherever one of the columns does not have a value it will give out Null.

Thank you for any help.

P.s these are Estimates in the data so do not get confused with 2014 values existing in 2012-01-31 etcetera.

So you just want to combine the rows of the four results sets into one result set? Can you use the `union` or `union all` keywords? https://learn.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-union-transact-sql — user2023861, Jun 26 '17 at 14:50
Also, it looks like the only difference between your queries is the `where Y.Date_Year` part. If that's the case, just do this `where Y.Date_Year in (2014, 2015, ...)` — user2023861, Jun 26 '17 at 14:56
Union as far as I understand does not help me with creating the different columns and populating them with the availble values (and making the others null). The Where Y.Date_Year in (xxx,yyy) gives me one longer column but it has the same short coming as union (as I understand it anyway). I am using Microsfot SQL Server Management Studio if that helps — Cedric Vongheer, Jun 26 '17 at 15:00

score 1 · Accepted Answer · answered Jun 26 '17 at 15:00

You are looking for conditional aggregation or a pivot query. I am more used to the former, so here it is:

select 
  m.date_month,
  sum(case when y.date_year = 2014 then n.ebit end) / 
   sum(case when y.date_year = 2014 then s.sales end) as "EBIT/Sales 2014",
  sum(case when y.date_year = 2015 then n.ebit end) / 
   sum(case when y.date_year = 2015 then s.sales end) as "EBIT/Sales 2015",
  sum(case when y.date_year = 2016 then n.ebit end) / 
   sum(case when y.date_year = 2016 then s.sales end) as "EBIT/Sales 2016",
  sum(case when y.date_year = 2017 then n.ebit end) / 
   sum(case when y.date_year = 2017 then s.sales end) as "EBIT/Sales 2017"
from ebit as n 
inner join sales as s on  s.company_id = n.company_id
                      and s.date_month_id = n.date_month_id 
                      and s.date_year_id = n.date_year_id
inner join date_year as y on y.date_year_id = n.date_year_id
inner join date_month as m on m.date_month_id = n.date_month_id
where y.date_year in (2014, 2015, 2016, 2017)
and n.ebit <> 0 
and s.sales <> 0
group by m.date_month;

Great, this did exactly what I wanted. I've not seen the "Case when, then, end" syntax before but it worked great. Thank you — Cedric Vongheer, Jun 26 '17 at 15:05

SQL - join multiple Select statements

1 Answers1