Check if row exists and if it doesn't output a specific value

Question

I have this query wherein what I'm trying to do is if the id and the value is null it will just show the value 0.111 on where the row is null.

SELECT FROM_UNIXTIME(t_stamp/1000, '%m/%d/%Y %l:%i %p') as t_stamp,

    ROUND(MAX(
            CASE 
            WHEN id = "731" AND value IS NULL THEN 0.11
            WHEN id = "731" THEN value
            END
            ),3
        ) as Val1,

    ROUND(MAX(
            CASE 
            WHEN id = "732" AND value IS NULL THEN 0.11
            WHEN id = "732" THEN value
            END
            ),3
        ) as Val2

FROM table1
WHERE id IN ("731", "732")  and

from_unixtime(t_stamp/1000) BETWEEN "2014-11-01 09:00:00" AND "2014-11-01 12:00:00"

GROUP BY DATE(from_unixtime(t_stamp/1000)), HOUR(from_unixtime(t_stamp/1000)), MINUTE(from_unixtime(t_stamp/1000)) DIV 1

I've been trying this a few times and its not working and the reason is when i checked the database, the problem was the row is not null, the reason why its not following the first 'WHEN' is because the row doesnt exist.

The data looks like this

|       t_stamp         |   Val1    |   Val2    |
|   11/01/2014 9:00 AM  |   0.022   |   0.044   |
|   11/01/2014 9:01 AM  |   0.023   |   0.045   |
|   11/01/2014 9:02 AM  |   0.022   |   0.044   |
|   11/01/2014 9:03 AM  |   0.022   |   0.044   |
|   11/01/2014 9:04 AM  |   0.022   |   0.044   |
|   11/01/2014 9:05 AM  |   NULL    |   0.046   |
|   11/01/2014 9:06 AM  |   0.023   |   0.040   |

There is no row for 9:05 AM. This is the output I was hoping for:

|       t_stamp         |   Val1    |   Val2    |
|   11/01/2014 9:00 AM  |   0.022   |   0.044   |
|   11/01/2014 9:01 AM  |   0.023   |   0.045   |
|   11/01/2014 9:02 AM  |   0.022   |   0.044   |
|   11/01/2014 9:03 AM  |   0.022   |   0.044   |
|   11/01/2014 9:04 AM  |   0.022   |   0.044   |
|   11/01/2014 9:05 AM  |   0.11    |   0.046   |
|   11/01/2014 9:06 AM  |   0.023   |   0.040   |

I checked the val for the id 731 and did a separate SELECT with just that ID and found that there's no row for the time 9:05 AM but in 732 there is.

Is this possible without actually doing an INSERT and just output that 0.11 value?

Answer 1

I think your logic is a bit off. It is looking for a NULL value in a row with 731. It is not looking for a missing row. Instead, try this:

COALESCE(ROUND(MAX(CASE WHEN id = "731" THEN value END), 3), 0.11)