Using GROUP BY and ORDER BY on an INNER JOIN SQL query

Question

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:

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

You can see the query working in a fiddle here.

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:

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

You can see the amended query in action here.

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:

╔════════╦═══════════╦═══════════╦══════════════╦══════════════╗
║ 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 ║
╚════════╩═══════════╩═══════════╩══════════════╩══════════════╝

Answer 1

Unless I am missing something in the requirements, what you need to do is get a list of the clients and the dates and then join that to your subqueries. So your query will be:

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 
(
  select distinct c.Client, d.Month
  from clients c
  cross join
  (
    select SUBSTR(Date, 1, 7) as Month
    from work_times
    union 
    select SUBSTR(Date, 1, 7) as Month
    from expenses
  ) d
) 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,Client
) b 
  ON a.Client = b.Client
  and a.month = b.month
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
  and a.month = c.month
WHERE   b.Client IS NOT NULL OR
        c.Client IS NOT NULL
order by a.month, a.client

See SQL Fiddle with Demo.

The result is:

| CLIENT |   MONTH | COUNTWORK | WORKTOTAL | COUNTEXPENSE | EXPENSETOTAL |
--------------------------------------------------------------------------
|      A | 2013-01 |         1 |        10 |            1 |           10 |
|      B | 2013-01 |         1 |        20 |            0 |            0 |
|      A | 2013-02 |         1 |        15 |            0 |            0 |
|      B | 2013-02 |         0 |         0 |            1 |           20 |
|      C | 2013-02 |         1 |        10 |            0 |            0 |