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.