1

I have a bit of SQL code that look similar to this:

SELECT 
sum(case when latitude = '0' then 1 else 0 end) as count_zero, 
sum(case when latitude is NULL then 1 else 0 end) as count_null,
sum((case when latitude = '0' then 1 else 0 end) + (case when latitude is NULL then 1 else 0 end)) as total_zero,
count(latitude) as count_not_nulls, 
count(*) as total
FROM sites_database;

Is there a "cleaner" way to write this same query. I have tried using the "sum" expression using the column alias, something like:

Sum(count_zero + count_null) as total_null

But this doesn't seem to work for some reason

2
  • not a "GIS" question so will get closed, but think about what you want your output to look like - your total_null column can be done using a subquery – DPSSpatial_BoycottingGISSE Dec 17 '19 at 23:40
  • Sorry, I will post this in a different forum! Thanks! – Nic Pickers Dec 17 '19 at 23:44
0

I think it's fine if it works how you want it to. Maybe you could use window functions/ partitions for this? Something like:

select 
    (select distinct count(*) over(partition by latitude) from sites_database where latitude = 0) as count_zero, 
    (select distinct count(*) over(partition by latitude) from sites_database where latitude is null) as count_null,
    (select distinct count(*) over(partition by latitude) from sites_database where latitude = 0 or latitude is null) as total_zero, 
    (select count(latitude) from sites_database) as count_not_null,
    (select count(*) from sites_database) as total;

However, I'm not sure this will be any 'cleaner'.

Not the answer you're looking for? Browse other questions tagged or ask your own question.