Oracle Count even if nothing is there

So i have this query:

SELECT p.*, d.reviews, TRUNC(ds.ratingAvg, 2) as ratingAvg
  FROM place p, 
    (SELECT pid,
      COUNT(rating) as ratings, 
      COUNT(review) as reviews
      FROM describes 
      GROUP BY pid) d,
    (SELECT pid, AVG(rating) as ratingAvg FROM describes GROUP BY pid) ds
  WHERE d.pid = p.pid AND d.pid = ds.pid
;

It will return stuff from place and then d is another table describes. It will get the number of reviews the place has and the average rating. This works perfectly fine as long as there is something in the describes table. How can i go about getting the stuff that does not have anything in describes as well. Basically 0 rating or 0 reviews.

Thanks

Answer

Use coalesce() and LEFT JOIN:

SELECT p.*
     , coalesce(d.reviews, 0)     AS reviews
     , coalesce(ds.rating_avg, 0) AS rating_avg
FROM   place p
LEFT   JOIN (
   SELECT pid
   --   , COUNT(rating) AS ratings  -- unused?
        , count(review) AS reviews
   FROM   describes
   GROUP  BY pid
   ) d ON d.pid = p.pid
LEFT   JOIN (
   SELECT pid
        , trunc(avg(rating), 2) AS rating_avg
   FROM   describes
   GROUP  BY pid
   ) ds ON ds.pid = p.pid;

Note how I use ON ds.pid = p.pid in the second JOIN and not ON ds.pid = d.pid like you had.

If the third relation is left-joined to the second which in turn is left-joined to the first, you lose rows from the third relation if none can be found in the second. Doesn’t make any difference in this particular case, because we have two unconditional queries on the same table – which should be simplified:

SELECT p.*
     , coalesce(d.reviews, 0)   AS reviews
     , coalesce(d.rating_avg, 0) AS rating_avg
FROM   place p
LEFT   JOIN (
   SELECT pid
   --   , COUNT(rating) AS ratings  -- unused?
        , count(review) AS reviews
        , trunc(avg(rating), 2) AS rating_avg
   FROM   describes
   GROUP  BY pid
   ) d ON d.pid = p.pid;