title: Using GROUP BY and ORDER BY on an INNER JOIN SQL query
body: <p>I am using the following query to group work times and expenses for clients from three tables, one for clients, one for work times and one for expenses:</p>
  
  <pre><code>SELECT  a.*,
          COALESCE(b.totalCount, 0) AS CountWork,
          COALESCE(b.totalAmount, 0) AS WorkTotal,
          COALESCE(c.totalCount, 0) AS CountExpense,
          COALESCE(c.totalAmount, 0) AS ExpenseTotal
  FROM    clients A
          LEFT JOIN
          (
              SELECT  Client, 
                      COUNT(*) totalCount,
                      SUM(Amount) totalAmount
              FROM    work_times
              WHERE   DATE BETWEEN '2013-01-01' AND '2013-02-01'
              GROUP   BY Client
          ) b ON a.Client = b.Client
          LEFT JOIN
          (
              SELECT  Client, 
                      COUNT(*) totalCount,
                      SUM(Amount) totalAmount
              FROM    expenses
              WHERE   DATE BETWEEN '2013-01-01' AND '2013-02-01'
              GROUP   BY Client
          ) c ON a.Client = c.Client
  WHERE   b.Client IS NOT NULL OR
          c.Client IS NOT NULL
  </code></pre>
  
  <p>You can see the query working in a fiddle <a href="http://www.sqlfiddle.com/#!2/fd61b/5" rel="nofollow">here</a>.</p>
  
  <p>I am trying to amend this query so that there is a row for each client for each month sorted by month and then client.  I am trying to do so with the following amended query:</p>
  
  <pre><code>SELECT  a.*,
          COALESCE(b.totalCount, 0) AS CountWork,
          COALESCE(b.totalAmount, 0) AS WorkTotal,
          COALESCE(c.totalCount, 0) AS CountExpense,
          COALESCE(c.totalAmount, 0) AS ExpenseTotal
  FROM    clients A
          LEFT JOIN
          (
              SELECT  Client, 
                      COUNT(*) totalCount,
                      SUM(Amount) totalAmount,
                      SUBSTR(Date, 1, 7) as Month
              FROM    work_times
              GROUP   BY Month,Client
              ORDER BY Month
          ) b ON a.Client = b.Client
          LEFT JOIN
          (
              SELECT  Client, 
                      COUNT(*) totalCount,
                      SUM(Amount) totalAmount,
                      SUBSTR(Date, 1, 7) as Month
              FROM    expenses
              GROUP   BY Month,Client
              ORDER BY Month,Client
          ) c ON a.Client = c.Client
  WHERE   b.Client IS NOT NULL OR
          c.Client IS NOT NULL
  </code></pre>
  
  <p>You can see the amended query in action <a href="http://www.sqlfiddle.com/#!2/fd61b/4" rel="nofollow">here</a>.</p>
  
  <p>It's not working quite right though.  Only one row is returned for Client B even though there is a work time in January 2013 and an expense in February 2013 (so there should be 2 rows) and it appears that the rows are being ordered by Client as opposed to Month.  Could someone suggest how to amend the query to get the desired output which for the example on the second fiddle would be:</p>
  
  <pre><code>╔════════╦═══════════╦═══════════╦══════════════╦══════════════╗
  ║ CLIENT ║ COUNTWORK ║ WORKTOTAL ║ COUNTEXPENSE ║ EXPENSETOTAL ║
  ╠════════╬═══════════╬═══════════╬══════════════╬══════════════╣
  ║ A      ║         1 ║        10 ║            1 ║           10 ║
  ║ B      ║         1 ║        20 ║            0 ║            0 ║
  ║ A      ║         1 ║        15 ║            0 ║            0 ║
  ║ B      ║         0 ║        0  ║            1 ║           10 ║
  ║ C      ║         1 ║        10 ║            0 ║            0 ║
  ╚════════╩═══════════╩═══════════╩══════════════╩══════════════╝
  </code></pre>