sql join data from two tables

Question

I wonder if someone help me to join data from two tables...spending all the day didn't manage...

Code 1 selects: Year | Turnover1 | Quantity1 | EurPerOrder1

SELECT Year(table1.ContractDate) AS Year,
  Sum(table1.TPrice) AS Turnover1,
  Count(table1.id) AS Quantity1,
  ROUND(Sum(table1.TPrice) / Count(table1.id), 0) AS EurPerOrder1
FROM table1
GROUP BY Year(table1.ContractDate) * 100
ORDER BY table1.ContractDate DESC

Code2 selects: Year | Turnover2 | Quantiry2 | EurPerOrder2

SELECT Year(table2.date) AS Year,
  Sum(table2.price) AS Turnover2,
  Count(table2.rid) AS Quantiry2,
  ROUND(Sum(table2.price) / Count(table2.rid), 0) AS EurPerOrder2
FROM table2
GROUP BY Year(table2.date) * 100
ORDER BY table2.date DESC

And I need to join data like:

I need to have all data from both tables grouped by years. Even table2 doesnt have year 2013 anyway I would like it showed 0 or empty... I have tried different ways using examples but nothing worked so I think the problem can occur because second table doesn't have all the years which are on table1...

Which is your DBMS? Also you need only matching by `YEAR` rows or also need all rows from `table1`? — Bogdan Bogdanov, Aug 02 '15 at 17:56
Just update question so we know if you will use left or inner join. Good explanation can be found [here](http://stackoverflow.com/questions/38549/difference-between-inner-and-outer-joins) — Bogdan Bogdanov, Aug 02 '15 at 18:00
I use mysql database. EurPerOrder3 was my mistake, I have already corrected... Thanx! — tester, Aug 02 '15 at 18:20
I see that you did not approve your anwers. Please if the question solves your problem approve answer. — Bogdan Bogdanov, Aug 02 '15 at 18:25

score 0 · Answer 1 · edited May 23 '17 at 11:43

First: you can read pretty good explanation about the JOINS here

Ok, according the question you need LEFT JOIN. This means all data from table1 and only matching data from table2.

The SELECT must look like:

SELECT Year(table1.ContractDate) AS Year,
  Sum(table1.TPrice) AS Turnover1,
  Count(table1.id) AS Quantiry1,
  ROUND(Sum(table1.TPrice) / Count(table1.id), 0) AS EurPerOrder1, 
    Sum(table2.price) AS Turnover2,
    Count(table2.rid) AS Quantiry2, 
  ROUND(Sum(table2.price) / Count(table2.rid), 0) AS EurPerOrder2 
FROM 
    table1 t1
    LEFT JOIN table2 t2 ON Year(table1.ContractDate) = Year(table2.date)
GROUP BY 
    Year(table1.ContractDate) * 100, Year(table2.date) * 100
ORDER BY 
    table1.ContractDate DESC, table2.date DESC

Of course you need to process NULL values. See link

Please check SQL and correct it if there are erreors. I don't have live data to check (by running it).

Thank you very much! I am not sure if I need lift join but I would need to have all data from both tables grouped by years. Even table2 doesnt have year 2013 anyway I would like it showed 0 or empty... — tester, Aug 02 '15 at 18:18
Ok, @tester, if the answer solves your problem, please don't forget to approve it. And when you have reputation you can vote up. Also you have to study about `JOINs` this is a fundamental topic in `SQL`. — Bogdan Bogdanov, Aug 02 '15 at 18:19

sql join data from two tables

1 Answers1