why is this postgresql query returning only zeros?
You know you don't need to use all those subqueries, right?

select g.ad_id, count(1) as total,
count(case when utc_timestamp &gt; '2017-11-01 00:00:00' AND utc_timestamp &lt; '2017-11-01 23:59:59' then 1 end) NOV1,
count(case when utc_timestamp &gt; '2017-11-02 00:00:00' AND utc_timestamp &lt; '2017-11-02 23:59:59' then 1 end) NOV2,
...
FROM geoData g
group by g.ad_id ;