postgresql query not returning expected result

Question

I have formed this postgresql query to return some result:

Select 
  TblDailyRch.viewing_date1, 
  TblDailyRch.channel_name1, 
  ((TblDailyRch.Reach/tblUniqViewers4AllChannels.ReachAll) * 100) "Reach %"
From
  (Select 
     viewing_date viewing_date1, 
     channel_name channel_name1, 
     count(distinct party_wid) Reach 
   From 
     tv_viewing_sessions_daily_aggregate 
   Where 
     viewing_date between '20171101' and '20171102' 
   Group By 
     viewing_date1, 
     channel_name 
   Order BY 
     viewing_date1, 
     channel_name1) TblDailyRch,
   (Select 
      viewing_date viewing_date2, 
      count(distinct party_wid) ReachAll 
    From 
      tv_viewing_sessions_daily_aggregate 
    Where 
      viewing_date between '20171101' and '20171102' 
    Group By 
      viewing_date2 Order BY viewing_date2) tblUniqViewers4AllChannels 
Where 
  tblUniqViewers4AllChannels.viewing_date2 = TblDailyRch.viewing_date1;

Below is the sample result on running only the first query:

 viewing_date |       channel_name       | reach
--------------+--------------------------+--------
 20171101     | 3E                       |  25133
 20171101     | 4FM                      |    253
 20171101     | 4MUSIC                   |   1243
 20171101     | 98FM                     |    236
 20171101     | ALIBI                    |   3468

Below is the sample result on running the second query:

viewing_date2 | reachall
---------------+----------
 20171101      |   115623
 20171102      |   113898

But when I run the complete query, i get below result:

 viewing_date |       channel_name       | Reach %
--------------+--------------------------+----------
 20171101     | 3E                       |        0
 20171101     | 4FM                      |        0
 20171101     | 4MUSIC                   |        0
 20171101     | 98FM                     |        0

Logically, the query seems correct but I am unable to figure out why on running the complete query, the third columns "Reach %" give result as '0' instead of the expected calculated percent value?

Vesa Karjalainen · Accepted Answer · 2018-11-14 11:18:10Z

3

Both of those COUNT()s return an integer, and integer division doesn't return decimals. Casting one of them to float will force a float division, returning what you expected.

For example:

count(distinct party_wid)::float Reach

answered Nov 14, 2018 at 11:18

Vesa Karjalainen

1,1058 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

postgresql query not returning expected result

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related