CODEWITHSUNDEEP
sqloracle-database
user9517536248
user9517536248

Reputation: 355

can avg and count work together in sql?

I have two tables in my database and they look like this

create table restaurants(
restID number(8) NOT NULL,(PK)
name varchar(50),
photo varchar(50))

and

create table review_Restaurants(
commentID number(8) NOT NULL,
reviewDate timestamp,
commentValue varchar(100),
rating number(1),
userName varchar (25) not null,
restID number(8) not null,);

and what i want to do is get the best restaurant(higher ratings and reviews) images out from database and show it on screen.

The way i approached this problem is i'll calculate the avg rating for every rest which ever greater than 3 will be considered and also those who have comments more than 5.

select ((avg)rating > 3) as "rating",(count(reviews) > 5) as "review"
from review_restaurant
group by restID;

but its not working. What am i doing wrong?

Upvotes: 0

Views: 297

Answers (1)

gvee
gvee

Reputation: 17161

SELECT restID
     , Avg(rating) As "rating"
     , Count(reviews) As "review"
FROM   review_restaurant
GROUP
    BY restID
HAVING Avg(rating) > 3
AND    Count(reviews) > 5

If you were to drop the HAVING clause off the above statement it would return all restaraunt reviews and their average rating and number of reviews.

The HAVING clause is very similar to a WHERE clause but the subtle difference is that the HAVING clause applies after the aggregation has occurred.

Another alternative approach is to use a subquery

SELECT restID
     , rating
     , review
FROM   (
        SELECT restID
             , Avg(rating) As "rating"
             , Count(reviews) As "review"
        FROM   review_restaurant
        GROUP
            BY restID
       ) As a_subquery
WHERE  rating > 3
AND    reviews > 5

The same logic as using the HAVING clause applies here too... We calculate all the average ratings and number of reviews and then we apply our criteria.

If you want to retrieve the corresponding information from the restaurants table then once we need to make our query above a subquery and join it back to the restaurants table.

SELECT restaurants.restID
     , restaurants.name
     , restaurants.photo
     , top_restaurants.rating As average_rating
     , top_restaurants.review As number_of_reviews
FROM   restaurants
 INNER
  JOIN (
        SELECT restID
             , Avg(rating) As "rating"
             , Count(reviews) As "review"
        FROM   review_restaurant
        GROUP
            BY restID
        HAVING Avg(rating) > 3
        AND    Count(reviews) > 5
       ) As top_restaurants
    ON top_restaurants.restID = restaurants.restID

Upvotes: 3

Related Questions