SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    Quake 1 Addict CreedFeed's Avatar
    Join Date
    Feb 2002
    Location
    Milwaukee, WI
    Posts
    296
    Mentioned
    0 Post(s)
    Tagged
    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:

    Code MySQL:
    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:

    ABC 1930
    GHI 765

    What I'd like to get:

    ABC 1930
    DEF 0
    GHI 765

    Any help?
    -- Steve Caponetto
    Quake 1 Resurrection :: CreedFeed

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,218
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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)

    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Quake 1 Addict CreedFeed's Avatar
    Join Date
    Feb 2002
    Location
    Milwaukee, WI
    Posts
    296
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?
    -- Steve Caponetto
    Quake 1 Resurrection :: CreedFeed

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,218
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    IF is proprietary mysql syntax, i prefer standard sql --

    COALESCE(SUM(data.total),0) AS total
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •