No More Solutions

You can find a lot of debugging and deploying problems while developing applications in .NET and Visual Basic here. There are also some querying tips in SQL and typical source codes which might be useful shared here.

Playing with Query - Grouping

Query Language : SQL

Simply saying, here are the tables:
table users (userid, name)
table book (bookid, bookname, author)
table review (reviewId, UserId, BookId, Comment, ReviewDate)

Question: Find users who have reviewed 2 different books on the same day!

Query for creating tables:
create table users (userid int , [name] varchar(50))
create table book (bookid int, bookname varchar(50), author varchar(50))
create table review (reviewId int , UserId int, BookId int, Comment varchar(50), ReviewDate datetime)



Query for data sample:
insert into users values (1, 'A')
insert into users values (2, 'B')
insert into users values (3, 'C')

insert into book values (1, 'Book A', 'Author A')
insert into book values (2, 'Book B', 'Author B')
insert into book values (3, 'Book C', 'Author C')

insert into review values (1, 1, 1, 'AAAA', getdate()-5)
insert into review values (2, 1, 2, 'AAAA', getdate()-5)
insert into review values (3, 1, 3, 'AAAA', getdate()-5)
insert into review values (4, 2, 1, 'AAAA', getdate()-4)
insert into review values (5, 2, 2, 'AAAA', getdate()-4)
insert into review values (6, 2, 1, 'AAAA', getdate())
insert into review values (7, 3, 2, 'AAAA', getdate())
insert into review values (8, 3, 1, 'AAAA', getdate())



By using the query below:
select a.userid, a.userid, b.name, a.bookid, c.bookname, a.reviewdate
from review a
left join users b on b.userid = a.userid
left join book c on c.bookid = a.bookid



The resulting records will be:


Answer:
select * from
(
   select userid, bookSum = count(userid), reviewDate = convert(char(10), reviewdate, 105)
   from review
   group by userid, convert(char(10), reviewdate, 105)
) a
where bookSum = 2


or

select userid, bookSum = count(bookid), reviewDate
from review
group by userid, convert(char(10), reviewdate, 105)
having count(bookid) = 2

And the resulting records will be:



PS: Convert to type 105 is only aimed to convert the type of the date to dd-MM-yyyy so that the date to be compared does not include the time.
Bookmark and Share

0 comments:

Post a Comment