PHP [SOLVED]: MySQL SUM 0 for no result on JOIN

PHP [SOLVED]: MySQL SUM 0 for no result on JOIN

Home Forums Scripting PHP Tutorials PHP [SOLVED]: MySQL SUM 0 for no result on JOIN

Tagged: , , ,

Viewing 2 posts - 1 through 2 (of 2 total)
  • Author
    Posts
  • #245540

    Cloudy Point
    Keymaster

    QuestionQuestion

    I have two tables, ‘authorization’ and ‘transaction’.

    authorization table

    Auth_ID | User_ID | Auth_Hours
    -------------------------------
    5       | 1       | 60
    6       | 2       | 40
    7       | 3       | 50
    

    transaction table

    Auth_ID | User_ID | Used_Hours
    -------------------------------
    5       | 1       | 5
    6       | 2       | 2
    5       | 1       | 20
    6       | 2       | 17
    5       | 1       | 11
    6       | 2       | 9
    

    I want my query to sum Used_Hours and group by Auth_ID and User_ID from the transaction table. Here’s the catch – I also want the query result to show users who have not used any of their Auth_Hours with a 0. See below:

    QUERY

    SELECT a.Auth_ID, a.USER_ID, a.Auth_Hours, SUM(t.Used_Hours)
    FROM AUTHORIZATION a
    JOIN TRANSACTION t
    on a.Auth_ID = t.Auth_ID
    and a.User_ID = t.User_ID
    GROUP BY a.Auth_ID, a.USER_ID, a.Auth_Hours
    

    Actual Result

    Auth_ID | User_ID | Auth_Hours | Total Hours Used
    -------------------------------------------------
    5       | 1       | 60         | 36
    6       | 2       | 40         | 28
    

    Wanted Result

    Auth_ID | User_ID | Auth_Hours | Total Hours Used
    -------------------------------------------------
    5       | 1       | 60         | 36
    6       | 2       | 40         | 28
    7       | 3       | 50         | 0
    

    I would imagine the query to be relatively simple.

    #245541

    Cloudy Point
    Keymaster

    Accepted AnswerAnswer

    The JOIN statement is a shortcut for INNER JOIN which returns rows records that have matching values in both tables. If you want to return all records from the one table and the matching records from the other table, then you should use an outer join (LEFT [OUTER] JOIN or RIGHT [OUTER] JOIN). Then you can use the IFNULL() or COALESCE()functions to convert NULLs to zeros:

    SELECT a.Auth_ID, a.USER_ID, a.Auth_Hours, IFNULL(SUM(t.Used_Hours), 0) AS 'Total Hours Used'
    FROM authorization a
    LEFT JOIN transaction t ON a.Auth_ID = t.Auth_ID AND a.User_ID = t.User_ID
    GROUP BY a.Auth_ID, a.USER_ID, a.Auth_Hours
    

    Notice that I used single quotes to assign a string with spaces as a name to the total field (as you used in your examples). This will work in all databases. In MySQL you can also use back ticks, but that only works in MySQL.

    Here is a good illustration about the different types of joining tables.

    Source: https://stackoverflow.com/questions/48026679/mysql-sum-0-for-no-result-on-join
    Author: Racil Hilan
    Creative Commons License
    This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic.