INNER JOIN query using three tables to return Count and Sum of rows that match criteria

inner-join, multiple-tables, sql

Solution

You need to separately calculate the values in a subquery. The purpose of `WHERE` clause on the outer most query is to filter out records that has record atleast on one table. So in this case, `Client D` will not be shown on the result list.

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

- SQLFiddle Demo

UPDATE

╔════════╦═══════════╦═══════════╦══════════════╦══════════════╗
║ CLIENT ║ COUNTWORK ║ WORKTOTAL ║ COUNTEXPENSE ║ EXPENSETOTAL ║
╠════════╬═══════════╬═══════════╬══════════════╬══════════════╣
║ A      ║         2 ║        25 ║            1 ║           10 ║
║ B      ║         1 ║        20 ║            1 ║           20 ║
║ C      ║         1 ║        10 ║            0 ║            0 ║
╚════════╩═══════════╩═══════════╩══════════════╩══════════════╝

Problem

I am trying to join the data from three SQL tables. The tables are in the following format: clients ``` ╔════════╗ ║ CLIENT ║ ╠════════╣ ║ A ║ ║ B ║ ║ C ║ ║ D ║ ╚════════╝ ``` work_times ``` ╔════════╦══════════╦════════╦════════════╗ ║ Client ║ Work ║ Amount ║ Date ║ ╠════════╬══════════╬════════╬════════════╣ ║ A ║ Web Work ║ 10 ║ 2013-01-12 ║ ║ B ║ Research ║ 20 ║ 2013-01-20 ║ ║ A ║ Web Work ║ 15 ║ 2013-01-21 ║ ║ C ║ Research ║ 10 ║ 2013-01-28 ║ ╚════════╩══════════╩════════╩════════════╝ ``` expenses ``` ╔════════╦══════════╦════════╦════════════╗ ║ Client ║ Item ║ Amount ║ Date ║ ╠════════╬══════════╬════════╬════════════╣ ║ A ║ Software ║ 10 ║ 2013-01-12 ║ ║ B ║ Software ║ 20 ║ 2013-01-20 ║ ╚════════╩══════════╩════════╩════════════╝ ``` I would like a query that returns the Count and Sum of the work and expenses for each client, i.e.: ``` ╔════════╦═══════════╦═══════════╦══════════════╦══════════════╗ ║ CLIENT ║ COUNTWORK ║ WORKTOTAL ║ COUNTEXPENSE ║ EXPENSETOTAL ║ ╠════════╬═══════════╬═══════════╬══════════════╬══════════════╣ ║ A ║ 2 ║ 25 ║ 1 ║ 10 ║ ║ B ║ 1 ║ 20 ║ 1 ║ 20 ║ ║ C ║ 1 ║ 10 ║ 0 ║ 0 ║ ╚════════╩═══════════╩═══════════╩══════════════╩══════════════╝ ``` So far I have the following: ``` SELECT clients.Client, COUNT(distinct work_times.id) AS num_work, COUNT(expenses.id) AS num_expenses FROM clients INNER JOIN work_times ON work_times.Client = clients.Client INNER JOIN expenses ON expenses.Client = work_times.Client GROUP BY clients.Client ``` Which seems to be along the right lines but which skips clients for which there is no expense and seems to multiply the num_expenses by num_work. I would also like to add a WHERE clause to specify to only return the work times and expenses between two dates. What changes do I need to make to the query to get the desired output?

Original source

Related problems