SQL view, default value if null

Question

I have 3 tables: restaurants, addresses and rates. I want to have a select statement to pick up all names of the restaurants, cities where the restaurants are and average rates of that restaurants. I have written something like this:

SELECT res.name as name,
       AVG(rate) as rate,
       ad.city as city
FROM restaurants res
JOIN rates r
    on res.id = r.restaurantid
JOIN addresses ad
    on res.addressid = ad.id
GROUP BY res.name, ad.city

It works fine as long as all restaurants have at least one rate, if not, the restaurant is not in the result. How can I improve this query to get all the restaurants with their cities and average rate of that restaurants and if there is no rate for the restaurant let's say assign rate 0.0.

Use LEFT JOIN rates r instead of JOIN rates r.

Wiimm
– Wiimm

2019-12-30 22:10:29 +00:00
Commented Dec 30, 2019 at 22:10 — Wiimm
– Wiimm, Commented Dec 30, 2019 at 22:10

GMB · Accepted Answer · 2019-12-30 22:13:29Z

3

THis will do it: LEft join the rates table and when null replace with 0 the rate

SELECT res.name as name,
       AVG(coalesce(rate,0)) as rate,
       ad.city as city
FROM restaurants res
LEFT JOIN rates r
    on res.id = r.restaurantid
JOIN addresses ad
    on res.addressid = ad.id
GROUP BY res.name, ad.city

edited Dec 30, 2019 at 22:13

GMB

224k25 gold badges103 silver badges151 bronze badges

answered Dec 30, 2019 at 22:11

zip

4,0612 gold badges13 silver badges22 bronze badges

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

2 Comments

zip Over a year ago

nice catch GMB.

GMB Over a year ago

Probably you want COALESCE() around AVG(), not within (aggregate functions ignore NULLs).

Collectives™ on Stack Overflow

SQL view, default value if null

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related