Why does my conditional average query return 0 in MC Query?

Question

I have a table that I am trying to calculate average values based on conditional statements.

I have used conditional statements before so I know the function is supported, but for this particular query it doesn't appear to return the results I need.

The Query

SELECT avg(case when message <> '' then 1 else 0 end) as average, COUNT(*) as total
FROM [system_events] 
WHERE event = 'alert'

The Schema & Data

| customer_id                          | timestamp           | event    | message |
|--------------------------------------|---------------------|----------|---------|
| 0B526064-0A8C-661F-7390-11FDBAB88EB0 | 2020-07-08 21:29:27 | startup  |         |
| 38C84D13-85AB-75B4-D8AF-D3325AACD9D8 | 2019-12-11 22:40:20 | alert    | example |
| 3A84B088-E27B-6EB7-6252-03D293F90ABE | 2020-01-03 09:33:28 | shutdown |         |
| 7E65A716-0C3B-2CF9-DD42-FB573CEE5715 | 2021-01-31 15:37:48 | startup  |         |
| 84E52076-FD4F-F9C2-287F-2085CBD88082 | 2021-04-10 02:06:47 | alert    |         |
| 86B2B708-5AC1-C054-2B70-045F634A2946 | 2019-12-18 12:20:14 | response |         |
| 97B5B85A-C073-98F7-B47E-E5A4109301C7 | 2019-09-29 21:18:06 | response |         |
| 97E2FDBE-6BE8-006C-A160-74AD5AD61836 | 2021-05-18 07:22:58 | alert    |         |
| AC5D9448-12A5-8412-3B4D-AF8C6F1F5194 | 2020-03-21 14:09:53 | startup  |         |
| B1E6E69D-08DA-4AFE-F307-5F5890F5DB28 | 2020-01-24 03:26:05 | shutdown |         |
| BC237DCD-9FC9-E533-40FD-52EAEDD1AD7E | 2019-06-25 15:27:42 | response |         |
| C3985558-F109-353E-D93F-59E6B88D7EEB | 2020-07-24 01:43:45 | alert    |         |
| C60C2F18-5BE6-DBEC-A871-B2803CFB10EC | 2019-06-25 05:42:39 | startup  |         |
| FF7AAF72-0DBC-011F-D7F2-0C497811A810 | 2020-12-23 14:20:36 | alert    | example |

The Output

| average  | total |
|----------|-------|
| 0        | 5     |

The Expected Output

| average  | total |
|----------|-------|
| 0.4      | 5     |

When I replicate the same data and use something like SQLFiddle the correct results are shown.

Answer 1

Below query will give you the expected results -

SELECT ((CAST((count(case when message <> '' then 1 else NULL end)) AS DECIMAL(2,1))) / (CAST((count(event)) AS DECIMAL(2,1)))) as average, COUNT(*) as total
    FROM [system_events] 
    WHERE event = 'alert'

1. When working with decimal data types, you have to follow the same in queries

2. When you specify the decimal format for the below statement, it will give you the average as 1.0 (5 records with value 0 and 1/ count is 5)

   avg (case when message <> '' then 1 else 0 end)

3. (count(case when message <> '' then 1 else NULL end) will return the count as 2 and (count(event)) will return as 5