CODEWITHSUNDEEP
mysql
Bill Bonney
Bill Bonney

Reputation: 21

MySQL Return a 0 when row doesn't exist

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.

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.

 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

Upvotes: 0

Views: 1542

Answers (2)

Shadow
Shadow

Reputation: 34231

To me it seems that WHERE i.department = 11 condition effectively turns your left join into an inner join because the criterion applies to the right hand side table. This makes your entire case expression superfluous, since the else branch will never execute.

Honestly, I think you only have to move the above criterion into the join condition and then just apply a simple sum() with coalesce() for the null values:

     SELECT t.shift_seq,
           coalesce(Sum(i.amount),0) AS Amount
     FROM  a_ticket AS t
     LEFT JOIN a_item AS i ON i.ticket = t.ticket AND i.department = 11
     GROUP  BY t.shift_seq

Upvotes: 1

Bill Bonney
Bill Bonney

Reputation: 21

As Shadow pointed out in the comments above the simpler solution was to move the WHERE i.department = 11 into the JOIN clause. This allowed us to cut it down to the below SQL code instead of our original mess. This resulted in a NULL where there were no sales for the department, but that NULL was easily handled in the PHP that was running this. Thanks again Shadow.

SELECT t.shift_seq, 
ROUND(SUM(i.amount),2) AS Amount
FROM a_ticket AS t
LEFT JOIN a_item AS i 
ON i.ticket = t.ticket
AND i.department =11
GROUP BY t.shift_seq
ORDER BY t.shift_seq

Upvotes: 0

Related Questions