Prevent NULLs on select average values

I’m trying to figure out how to prevent NULLs on return from a select and do not return anything (0 rows) for that query where I provide incorrect state value as

select avg(lat) as lat, avg(lon) as lon from locations where city = "New York" and state = "NC"

I have tried something like

select case when avg(lat) != NULL then avg(lat) end as lat, case when avg(lon) != NULL then avg(lon) end as lon from locations where city ="New York" and state="NC"

but that didn’t work either.

Answer

Aggregate function withoutgroup by always returns a row. So you need to add some group by (for example, group by city) or having avg(lat) is not null to prevent output on an empty input dataset (filtered).

create table t
as
select *
from (values
  row(1, 'NY'),
  row(3, 'NY'),
  row(null, 'LA')
) as t(lat, state)

select state, avg(lat) as alat
from t
group by state

state |   alat
:---- | -----:
NY    | 2.0000
LA    |   null

/*No input rows - empty*/
select avg(lat)
from t
where state = 'AR'
group by state

| avg(lat) |
| -------: |

/*There's input row,
but the avg is empty - empty output*/
select avg(lat)
from t
where state = 'LA'
group by state

| avg(lat) |
| -------: |
|     null |

/*There's a row and avg is empty,
but we filter it out with having*/
select avg(lat)
from t
where state = 'LA'
group by state
having avg(lat) is not null

| avg(lat) |
| -------: |

db<>fiddle here