Count by Self Joining same table where one field is empty but another has value

I have a table that contains two sets of GPS coordinates, ones supplied by the customers the other captured by our field device.

The table’s name is customer, the field names are as follows:

ID

Latitude [Data from Customer]

Longitude [Data from Customer]

GPSLatitude [Data from field device]

GPSLongitude [Data from field device]

1. I would like to count all such rows where both Latitude and Longitude are either blank or contain value=0.
2. Then count all GPSLatitude, GPSLongitude whose ID equal to those records which were counted in #1 and which are not empty or do not contain value=0

Answer

I think you want conditional aggregation:

select count(*) cnt1, 
    sum(case when gpslatitude <> 0 and gpslongitude <> 0 then 1 else 0 end) cnt2
from customer 
where (latitude is null or latitude = 0)
  and (longitude is null or longitude = 0)

The query filters on customer longitude and latitude that are null or equal to 0. cnt1 gives you the count of such records. Then cnt2 computes how many record in the resultset have both device coordinates neither null nor 0.