1

I want to join 2 tables from 2 different queries side by side.

Q1= Employees sales value for one year

Q2= Employees sales values for another year

Expected output: Employee / TotalSalesValue_1996 / TotalSalesValue_ 1997

Below is my unsuccessful attempt, it seams to duplicate the Value of Q2.

select A1.Employee, A1.Title, A1.TotalSalesValue_1996, A2.TotalSalesValue_1997 
from 
( 
  select concat(e.titleofcourtesy,' ', e.firstname, e.lastname) as Employee, e.Title, sum((od.unitprice * od.quantity) - (1 * od.discount)) as TotalSalesValue_1996
  from employees as e 
  inner join orders as o on o.employeeid=e.employeeid
  inner join order_details as od on od.orderid=o.orderid
  where o.ShippedDate between '1996-01-01' and '1996-12-31'
  group by e.title, concat(e.titleofcourtesy,' ', e.firstname, e.lastname)
) as A1
,
(
  select concat(e.titleofcourtesy,' ', e.firstname, e.lastname) as Employee, e.Title, sum((od.unitprice * od.quantity) - (1 * od.discount)) as TotalSalesValue_1997
  from employees as e 
  inner join orders as o on o.employeeid=e.employeeid
  inner join order_details as od on od.orderid=o.orderid
  where o.ShippedDate between '1997-01-01' and '1997-12-31'
  group by e.title, concat(e.titleofcourtesy,' ', e.firstname, e.lastname)
) as A2;

*Please note that I used one of the free data bases available (northwind.sql)

1

Use a JOIN clause. So instead of the comma betwee the tables, put FULL OUTER JOIN. And then after A2 put ON A1.Employee = A2.Employee AND A1.Title = A2.Title.

But a better option might be to use a single query, with:

select 
   concat(e.titleofcourtesy,' ', e.firstname, e.lastname) as Employee, 
   e.Title, 
   sum(CASE WHEN o.ShippedDate between '1996-01-01' and '1996-12-31' THEN (od.unitprice * od.quantity) - (1 * od.discount) ELSE 0 END) as TotalSalesValue_1996, 
   sum(CASE WHEN o.ShippedDate between '1997-01-01' and '1997-12-31' THEN (od.unitprice * od.quantity) - (1 * od.discount) ELSE 0 END) as TotalSalesValue_1997
from employees as e 
inner join orders as o on o.employeeid=e.employeeid
inner join order_details as od on od.orderid=o.orderid
where o.ShippedDate between '1996-01-01' and '1996-12-31'
group by e.title, concat(e.titleofcourtesy,' ', e.firstname, e.lastname);
  • Thank you Rob, both of them work perfectly and your suggestion is much shorter and simpler. – Hamel Nov 23 '15 at 10:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.