Results 1 to 4 of 4
Dec 5, 2012, 12:06 #1
- Join Date
- Feb 2002
- Milwaukee, WI
- 0 Post(s)
- 0 Thread(s)
Display all derived fields in a query
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?
ABC 1930 GHI 765
What I'd like to get:
ABC 1930 DEF 0 GHI 765