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

Question

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

I use the following query to group work hours and costs for customers from three tables: one for customers, one for work hours and one for costs:

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 running in the script here .

I am trying to modify this query so that for each client there is a row for each month, sorted by month, and then to the client. I am trying to do this with the following modified request:

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 modified request in action here .

However, it does not work. For client B, only one row is returned, although in January 2013 there are working hours and expenses in February 2013 (therefore there should be 2 rows), and it seems that the rows are ordered by the client, and not a month. Can someone suggest how to change the query to get the desired result, which for example on the second fiddle will 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 ║
╚════════╩═══════════╩═══════════╩══════════════╩══════════════╝

+5

sql inner-join

Nick Feb 24 '13 at 17:22

source share

2 answers

, , ( ) . .

, B, C. B.month, B.client C.month .

BTW, C, . , DB2, select, .

0

Marlin Pierce 24 . '13 17:54

Taryn · Accepted Answer · 2013-02-24T18:30:54+0000

If I am missing something in the requirements, you need to get a list of clients and dates, and then join these subqueries. So your query will look like this:

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 .

Result:

| 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 |

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

More articles: