How to assign zero(0) when the avg of a particular field is null in PostgreSQL

Question

I have two tables:

Table user_ratings

id  home_info_id  ratings
1   1             3.5 
2   2             3.5
3   1             4
4   1             5
5   1             2
6   2             1
7   2             4

Table home_info:

id  home_name 
1   my_home
2   ur_home
3   his_home

As you can see 'my_home' and 'ur_home' has ratings but 'his_home' is not rated yet. I am calculating the avg of all homes, so I am getting avg of only two homes, i.e. 'my_home' and 'ur_home', as I said 'his_home' is not rated yet, so I am not getting 'his_home' in my query below. I want all the names of homes which are not rated yet. Here is my query:

select u.home_info_id
     , avg(u.ratings)
     , h.home_name
from user_ratings u
   , home_info h 
where h.id = u.home_info_id
group by u.home_info_id
       , h.home_name;

The output is something like this:

home_info_id  ratings  home_name 
1             4.83     my_home     
2             2.83     ur_home

But I want something like this:

 home_info_id  ratings  home_name 
 1             4.83     my_home     
 2             2.83     ur_home
 3             0        his_home

query is same for all, If i get some idea with mysql query i will implement in postgresql — Aruna Angadi, Apr 06 '15 at 10:37
Removed sql and mysql tags now please upvote my downvoted question please, now it has enough info.:-( — Aruna Angadi, Apr 06 '15 at 10:45
`user_review` or `user_ratings`? Please make the question consistent. — Erwin Brandstetter, Apr 06 '15 at 12:03
Question is understood by answerer and i got answer sir @ErwinBrandstetter — Aruna Angadi, Apr 06 '15 at 13:21
@arunaangadi: This is a public site. Questions are supposed to be useful to the general public. It's not a service to write queries for you. Please fix the question. — Erwin Brandstetter, Apr 06 '15 at 13:27
Fixed mr.@ErwinBrandstetter by the way I didnt mean to be public and private the main intension i think to be gain knowledge nobody is writing queries for me, I am here to acquire knowledge from the persons who wish to share. Thank you for the catching mistake. — Aruna Angadi, Apr 06 '15 at 13:48

potashin · Accepted Answer · 2015-04-06T10:57:06.793

1

You can use COALESCE with LEFT JOIN (instead of implicit INNER JOIN):

select h.id
     , coalesce(avg(u.ratings), 0)
     , h.home_name
from home_info h 
left join user_review u on h.id = u.home_info_id
group by h.id
       , h.home_name

edited Apr 06 '15 at 10:57

answered Apr 06 '15 at 10:37

potashin

44,205
11
83
107

Well I am getting field names for home_name and rating but home_info id blank – Aruna Angadi Apr 06 '15 at 10:39
@aruna angadi: Well, you can't get the result you are writing about, if you use `LEFT JOIN` as it leaves all records from `home_info` (rated `1`, `2` and unrated `3`). – potashin Apr 06 '15 at 10:42
So i will not get the hall_info_id field for unrated home?? – Aruna Angadi Apr 06 '15 at 10:44
@aruna angadi: Well, just retrieve `home_info.id` instead of `user_review.home_info_id`. – potashin Apr 06 '15 at 10:47
1

Many thanks....I am upvoting you, Sir please tell to upvote mine too somebody downvoted my question. thank you soo much – Aruna Angadi Apr 06 '15 at 10:49
Why do you chose to select h.id and h.home_name, but group by u.home_info_id and h.home_name? – jarlh Apr 06 '15 at 10:53
@jarlh: Well, because I _forgot_ to change `u.home_info_id` to `h.id` in the `GROUP BY` clause) – potashin Apr 06 '15 at 10:55
That was the problem @notulysses thanks for the help.. somebody please solve my this question please I am using codeigniter to take ip addresses but I am not getting ip address, getting as 0.0.0.0, nobody answered me please check out my quesion. link http://stackoverflow.com/questions/29367137/codeigniter-ci-sessions-table-storing-ip-address-as-0-0-0-0-why-even-when-hoste – Aruna Angadi Apr 06 '15 at 10:56
@notulysses To be precise : use an order by clause for your select ie `order by h.id` – Vivek S. Apr 06 '15 at 11:34

score 1 · Answer 2 · edited May 23 '17 at 12:14

When scanning the whole table or most of it, it is cheaper to aggregate before you join:

SELECT h.id, h.home_name
     , COALESCE(u.avg_rating, 0) AS avg_rating
FROM   home_info h
LEFT   JOIN (
   SELECT home_info_id AS id, avg(ratings) AS avg_rating
   FROM   user_review
   GROUP  BY 1
   ) u USING (id);

Test with EXPLAIN ANALYZE.

How to assign zero(0) when the avg of a particular field is null in PostgreSQL

2 Answers2