CODEWITHSUNDEEP
sqloracle-database
user2402107
user2402107

Reputation: 913

Sql Join between two tables with a group by

I am extremely new to SQL and I have been working on this problem for hours and I am so close but not quite there:

Three tables

Category

cid cname

Product

pid
cid
pname
brand
price

Review

rid
userid
pid
rdate
score rcomment

I am trying to

1:

select avg(score), review.pid
from review
join product
on review.pid = product.pid
group by review.pid;

2:

select * from product
join review
on product.pid = review.pid
where cid ='1';

Here is a fiddle: http://sqlfiddle.com/#!4/a6b30/1

Upvotes: 2

Views: 149

Answers (2)

Neria Nachum
Neria Nachum

Reputation: 1529

  1. You can add the pname to your GROUP BY clause and then to your SELECT clause. pid is unique for each product so it does not affecting the query at all, but let you add the name to your results.

    select avg(review.score), product.pname
from review
join product
on review.pid = product.pid
group by product.pname;

  2. In the following query I use a subquery to select only items with average score >= 4 using HAVING clause. Then I select only pid that appear in the subquery's result and adding the cid='1' part.

    select product.pname
from product
join review
on product.pid = review.pid
where cid ='1'
and pid IN (SELECT pid FROM review GROUP BY pid HAVING AVG(score) >= 4);

Upvotes: 1

schurik
schurik

Reputation: 7928

1.

select  p.pname, avg(r.score) 
from review r
join product p
on p.pid = r.pid
group by p.pname;

2. The following query returns the ids and the names of product under the category TV and with average score above 4.0

   select  p.pname,  avg(r.score)
    from 
        category c
    join product p on p.cid = c.cid 
    join review r  on r.pid = p.pid
    where c.cname = 'TV'
    group by 
      p.pname
    having  avg(r.score) > 4 ;

Upvotes: 0

Related Questions