1

So I have a table similar to this one. Each user has posted a review about one or more hotels(A,B,C,D) but on different dates so there are no duplicate tuples even though a person might have reviewed the same hotel more than once.

I need to count the number of DISTINCT hotels every user has reviewed using RELATIONAL ALGEBRA only. How can I do that?

enter image description here

example to show notations I use:

R = ƔUser,COUNT(Hotel_reviewed)->Num_Reviews (InitialRelation- table 1) 

would give the number of reviews by each user

The result should be the following table:

enter image description here

example to show notations I use:

R = ƔUser,COUNT(Hotel_reviewed)->Num_Reviews (InitialRelation- table 1) 

would give the number of reviews by each user

3

Besides the more compact syntax (from @McNets' answer):

select   User,
         count(distinct Hotel_Reviewed) HotelsReviewed
from     InitialRelation
group by User;

we can also do a projection first to find distinct User, Hotel_Reviewed pairs and then aggregate:

select   User,
         count(Hotel_Reviewed) as Hotels_Reviewed
from     
    ( select distinct
               User,
               Hotel_Reviewed
      from     InitialRelation
    ) as D
group by User ;

This leads us to the relational algebra notation:

R = Ɣ User, COUNT(Hotel_Reviewed) -> Hotels_Reviewed 
        (π User, Hotel_Reviewed (InitialRelation)) -> D
  • projection doesn't remove duplicates when we are talking about bags, right? – Sreten Jocić Apr 7 '17 at 8:59
  • 1
    But relations are not bags, they are sets. SQL needs SELECT DISTINCT because it doesn't apply the relational model accurately in that part and allows tables and result sets with multiple identical rows. – ypercubeᵀᴹ Apr 7 '17 at 9:00
0

You can get it by counting distinct hotels, grouped by user.

select   User,
         count(distinct Hotel_Reviewed) HotelsReviewed
from     your_table
group by User;
create table reviews([user] varchar(20), date_review date, hotel_reviewed varchar(10) );
insert into reviews values
('Joe',   '20170101', 'A'),
('Joe',   '20170201', 'A'),
('Tom',   '20170101', 'C'),
('Bryan', '20170101', 'B'),
('Bryan', '20170201', 'A'),
('Mike',  '20170101', 'B'),
('Mike',  '20170201', 'C'),
('Mike',  '20170301', 'D');
GO
select   [User],
         count(distinct Hotel_Reviewed) HotelsReviewed
from     reviews
group by [User];
GO
User  | HotelsReviewed
:---- | -------------:
Joe   |              1
Tom   |              1
Bryan |              2
Mike  |              3

dbfiddle here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.