title: Create one query to achieve same result of two
body: <p>I want get month list with teachers, expenses and comissions.
  Expense contains of installment paid to teacher and price for gived lessons.
  Commision is sum of 'commision per lessons' and 'month prices'. </p>
  
  <p>Schema:</p>
  
  <pre><code>CREATE TABLE contract
      (`id` int, `teacher` varchar(64), `lesson_price` int, `lesson_price_commision` int, `month_price` int)
  ;
  
  INSERT INTO contract
      (`id`, `teacher`, `lesson_price`, `lesson_price_commision`, `month_price`)
  VALUES
      (1, 'John', 50, 10, 0),
      (2, 'Paul', 40, 15, 0),
      (3, 'Meg', 0, 0, 800)
  ;
  
  CREATE TABLE installment
      (`id` int, `contract` int, `installment_price` int, `installment_date` date)
  ;
  
  INSERT INTO installment
      (`id`, `contract`, `installment_price`, `installment_date`)
  VALUES
      (1, 1, 300, '2015-09-28'),
      (2, 1, 400, '2015-09-30'),
      (3, 3, 500, '2015-09-28')
  ;
  
  CREATE TABLE lesson
      (`id` int, `contract` int, `lesson_date` date)
  ;
  
  INSERT INTO lesson
      (`id`, `contract`, `lesson_date`)
  VALUES
      (1, 1, '2015-08-16'),
      (2, 3, '2015-09-01'),
      (3, 2, '2015-09-02'),
      (4, 1, '2015-09-06')
  ;
  </code></pre>
  
  <p>I made it in two queries, but I must combine them. </p>
  
  <p>Query one:</p>
  
  <pre><code>SELECT teacher, SUM(lesson_price_commision) AS commision, SUM(lesson_price) AS expense
  FROM contract
  JOIN lesson ON contract.id=lesson.contract 
  WHERE MONTH(lesson_date) = 9 AND YEAR(lesson_date) = 2015
  GROUP BY teacher
  </code></pre>
  
  <p>Query two:</p>
  
  <pre><code>SELECT teacher, SUM(month_price) AS commision, SUM(installment_price) AS expense
  FROM installment
  JOIN contract ON contract.id=installment.contract 
  WHERE MONTH(installment_date) = 9 AND YEAR(installment_date) = 2015
  GROUP BY teacher
  </code></pre>
  
  <p>I constructed this:</p>
  
  <pre><code>SELECT teacher,
         (SUM(lesson_price_commision) + SUM(month_price)) AS commision,
         (SUM(lesson_price)+SUM(installment_price)) AS expense
  FROM contract
    JOIN lesson ON contract.id=lesson.contract 
    LEFT JOIN installment ON installment.contract = contract.id
  WHERE (MONTH(installment_date) = 9 AND YEAR(installment_date) = 2015)
     OR (MONTH(lesson_date) = 9 AND YEAR(lesson_date) = 2015)
  GROUP BY teacher
  </code></pre>
  
  <p>and result is:</p>
  
  <pre><code>teacher | comission | expense
  -----------------------------
  John    |    40     |  1600
  Paul    |    15     |  null
  Meg     |   800     |   500
  </code></pre>
  
  <p>but I am expecting:</p>
  
  <pre><code>teacher | comission | expense
  -----------------------------
  John    |    10     |  750
  Paul    |    15     |   40
  Meg     |    800    |  500
  </code></pre>
  
  <p>Fiddle: <a href="http://sqlfiddle.com/#!9/7247a/1" rel="nofollow">http://sqlfiddle.com/#!9/7247a/1</a></p>