SQL: How to have a row even if the SELECT CASE doesn't get triggered?

I have the following SELECT ...

    SELECT CASE WHEN cola < 0 THEN '-'
                WHEN cola > 0 THEN '+'
                ELSE '='
           END
           SUM(colb), SUM(colc), SUM(cold),
           MAX(CASE WHEN cola < 0 THEN 1
                    WHEN cola > 0 THEN 3
                    ELSE 2
              ) AS Sort
      FROM Table1
     WHERE this = that
  GROUP BY CASE WHEN cola < 0 THEN '-'
  开发者_开发问答              WHEN cola > 0 THEN '+'
                ELSE '=' END
  ORDER BY cold

This is what I'm getting:

cola    colb   colc   cold
-        1       2     1
+        13      0     3

This is what I'm wanting:

cola    colb   colc   cold
-        1       2     1
=        0       0     2
+        13      0     3

When I get a result set I have '+' and '-' rows but no '=' row because there weren't any '0' values to trigger the ELSE. How can I have it set so that if this is the case then it would still have a '=' row in my SELECT statement?

Thanks! *If more of the select is required just let me know.

SELECT
       foo.symbol,
       SUM(colb), SUM(colc), SUM(cold)
  FROM
        (SELECT '-' AS Symbol, 1 As Sort 
         UNION ALL SELECT '=', 2
         UNION ALL SELECT '+', 3
        ) foo
        LEFT JOIN
        Table1 ON foo.symbol = CASE WHEN cola < 0 THEN '-'
            WHEN cola > 0 THEN '+'
            ELSE '='
       END
 WHERE this = that
GROUP BY foo.symbol, foo.sort
ORDER BY foo.sort

You may need a derived table for Table1 too to apply the filter correctly. You can push th CASE into this

Edit, after comments, even simpler thanks to @WReach

SELECT
       foo.SignAndSort,
       SUM(colb), SUM(colc), SUM(cold)
  FROM
        (SELECT '-1' AS SignAndSort
         UNION ALL SELECT '0'
         UNION ALL SELECT '-1'
        ) foo
        LEFT JOIN
        Table1 ON foo.SignAndSort = SIGN(cola)
 WHERE this = that
GROUP BY foo.SignAndSort
ORDER BY foo.SignAndSort

It sounds like you want the 0 to be represented, even though the that value wasn't in the cola column.

My first approach would be to:

• gather the summation in a derived table (t)
• UNION that against another derived table with a single row containing your equals row. (t2)
• SELECT the MAX, which would filter out the duplicates when the UNION ALL produces a duplicate in the case that the equals actually existed in table t.

SELECT  MySymbol, MAX(MySumB) AS MySumB, 
                  MAX(MySumC) AS MySumC, 
                  MAX(MySumD) AS MySumD
FROM (    
        SELECT MySymbol,MySumB,MySumC,MySumD
        FROM (
                SELECT CASE WHEN cola < 0 THEN '-'
                        WHEN cola > 0 THEN '+'
                        ELSE '='
                   END  AS MySymbol,
                   SUM(colb) AS MySumB, SUM(colc) AS MySumC, SUM(cold) AS MySumD,
                   MAX(CASE WHEN cola < 0 THEN 1
                            WHEN cola > 0 THEN 3
                            ELSE 2
                      ) AS Sort
                 FROM Table1
                 WHERE this = that
                 GROUP BY CASE WHEN cola < 0 THEN '-'
                            WHEN cola > 0 THEN '+'
                            ELSE '=' END                
            UNION  ALL
            SELECT '=' AS MySymbol, 0,0,0,0
        )  t
) t2
GROUP BY MySymbol
ORDER BY MySumD

This could've been a bit tighter a solution if we knew the RDBMS you were using. The above is ANSI-compliant, so it'll work anywhere. A less-verbose solution likely exists for Oracle, SQL Server or MySQL, using temp tables or table variables. Since the RDBMS wasn't specified, I couldn't give a platform specific answer, and went with the generic approach.