why is this postgresql query returning only zeros? select g.ad_id, count(1) as total,
(select count(1) from geoData g1 WHERE g1.ad_id=g.ad_id and 
utc_timestamp &gt; '2017-11-01 00:00:00' AND utc_timestamp &lt; '2017-11-01 23:59:59') NOV1,
(select count(1) from geodata g2 where g2.ad_id=g.ad_id and 
utc_timestamp &gt; '2017-11-02 00:00:00' AND utc_timestamp &lt; '2017-11-02 23:59:59') NOV2,
(select count(1) from geodata g3 where g3.ad_id=g.ad_id and 
utc_timestamp &gt; '2017-11-03 00:00:00' AND utc_timestamp &lt; '2017-11-03 23:59:59') NOV3,
(select count(1) from geodata g4 where g4.ad_id=g.ad_id and 
utc_timestamp &gt; '2017-11-04 00:00:00' AND utc_timestamp &lt; '2017-11-04 23:59:59') NOV4,
(select count(1) from geodata g5 where g5.ad_id=g.ad_id and 
utc_timestamp &gt; '2017-11-05 00:00:00' AND utc_timestamp &lt; '2017-11-05 23:59:59') NOV5,
(select count(1) from geodata g6 where g6.ad_id=g.ad_id and 
utc_timestamp &gt; '2017-11-06 00:00:00' AND utc_timestamp &lt; '2017-11-06 23:59:59') NOV6,
(select count(1) from geodata g7 where g7.ad_id=g.ad_id and 
utc_timestamp &gt; '2017-11-07 00:00:00' AND utc_timestamp &lt; '2017-11-07 23:59:59') NOV7,
(select count(1) from geodata g8 where g8.ad_id=g.ad_id and 
utc_timestamp &gt; '2017-11-08 00:00:00' AND utc_timestamp &lt; '2017-11-08 23:59:59') NOV8,
(select count(1) from geodata g9 where g9.ad_id=g.ad_id and 
utc_timestamp &gt; '2017-11-09 00:00:00' AND utc_timestamp &lt; '2017-11-09 23:59:59') NOV9
FROM geoData g
group by g.ad_id ;


The psql says its ok, it takes almost an hour to run (it's 35gigs of data in the table) but it returns all zeros in the subqueries
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 ;