title: MySQL Return a 0 when row doesn't exist
body: <p>Below is an incredibly complex and hard to read SQL statement we needed to solve our problem and we are wondering if there was an easier way to do this that we just overlooked or didn't know.</p>
  
  <p>The Problem: We are creating a report that needs to sum sales data for a particular sales category and group that by category.  The issue is that when there is an instance of one of the shifts not having any sales in that category it returns nothing.  This doesn't work for us because this will cause data to not line up correctly with the shifts.  So all we need is for the query to return a zero for the shift if no instances of it exist.  We tried left joins, case statements, coalesce, unions etc...  What finally got to work is the monster below.  Any thoughts on how this could be done simpler would be greatly appreciated.</p>
  
  <pre><code> SELECT shift_seq,
         amount
  FROM   ((SELECT t.shift_seq,
                  CASE
                    WHEN EXISTS (SELECT t.shift_seq,
                                        i.amount
                                 FROM   a_item AS i,
                                        a_ticket AS t
                                 WHERE  i.ticket = t.ticket
                                        AND i.department = 11) THEN
                    Round(Sum(i.amount), 2)
                    ELSE 0.00
                  END AS Amount
           FROM   a_ticket AS t
                  LEFT JOIN a_item AS i
                         ON i.ticket = t.ticket
           WHERE  i.department = 11
           GROUP  BY t.shift_seq
           ORDER  BY t.shift_seq)
          UNION
          (SELECT t.shift_seq,
                  0 AS Amount
           FROM   a_ticket AS t)) AS a
  GROUP  BY shift_seq
  ORDER  BY shift_seq  
  </code></pre>