Display all derived fields in a query

Databases
1

I have a very big and complicated query, but the main idea of the query is:

SELECT
    t.field1, 
    SUM(t.total) AS total
FROM
(
    (
        SELECT
            CASE 
                WHEN field IN ( [list 1] ) THEN "ABC"
                WHEN field IN ( [list 2] ) THEN "DEF"
                ELSE "GHI"
            END AS field1,
            total as total
        FROM table1
        LEFT JOIN table2 ...
        WHERE
            (bunch of where clauses)
    )
    UNION ALL
    (
        SELECT
            CASE 
                WHEN field IN ( [list 1] ) THEN "ABC"
                WHEN field IN ( [list 2] ) THEN "DEF"
                ELSE "GHI"
            END AS field1,
            total as total
        FROM another_table
        LEFT JOIN yet_another_table ...
        WHERE
            (bunch of where clauses)
    )
) AS t
GROUP BY
    t.field1
ORDER BY
    FIELD(t.field1, "ABC", "DEF", "GHI")

I’m UNION’ing the results from two queries, then grouping by field1 and summing up the total field for each field1.

My question is - if one of the field1 values (say DEF) is not found, the query results do not include DEF. What I’d like is to always show each value of field1 and display 0 for the total if it wasn’t found. How would I do this?

Current Output:

[table=“width: 200, class: grid, align: left”]
[tr]
[td]ABC[/td]
[td]1930[/td]
[/tr]
[tr]
[td]GHI[/td]
[td]765[/td]
[/tr]
[/table]

What I’d like to get:

[table=“width: 200, class: grid, align: left”]
[tr]
[td]ABC[/td]
[td]1930[/td]
[/tr]
[tr]
[td]DEF[/td]
[td]0[/td]
[/tr]
[tr]
[td]GHI[/td]
[td]765[/td]
[/tr]
[/table]

Any help?

2

the straighforward answer is… using a LEFT OUTER JOIN

the left table would be a table containing all the codes you want to display, and the right table would be your data

you don’t actually have to have a permanent table for the codes (although it’s probably a good idea)

SELECT codes.field1
     , SUM(data.total) AS total
  FROM ( SELECT 'ABC' AS field1
         UNION ALL
         SELECT 'DEF'
         UNION ALL
         SELECT 'GHI' ) AS codes
LEFT OUTER
  JOIN ( /* your union query */ ) AS data
GROUP
    BY codes.field1

if you do decide to store a premanent table for your codes, it might be worth it to store the list of translations for each code, so that your queries can avoid the ugly CASE expressions, too

3

Awesome! Thank you very much, works great!

One follow up question - for the code which has no results, I’m getting NULL back instead of 0. Is the easiest way to get 0 instead of NULL to just do in the initial SELECT something like:

IF( SUM(data.total) IS NULL, 0, SUM(data.total)) AS total

Or is there a better way to handle that?

4

IF is proprietary mysql syntax, i prefer standard sql –

COALESCE(SUM(data.total),0) AS total