Problem with GROUP BY [message #326802] |
Thu, 12 June 2008 11:31  |
vinaypammi
Messages: 1 Registered: June 2008 Location: USA
|
Junior Member |
|
|
Hi,
I need a query which gives me the number of users with number of bookmarks less than 5 on the following table.
Bookmark_id user_id
------------ ----------
1 100
2 100
3 100
4 101
5 102
7 103
8 103
6 103
9 103
10 103
The output must be 3 users (namely 100, 101 and 102).
I am having trouble writing the GROUPBY clause and get the output. please help !!!!!!!!!
|
|
|
|
Re: Problem with GROUP BY [message #326859 is a reply to message #326802] |
Thu, 12 June 2008 20:56   |
cnvegnix
Messages: 21 Registered: June 2008
|
Junior Member |
|
|
Preparing the testing data:
create table bookmarks(bookmark_id number,user_id number);
insert into bookmarks(bookmark_id,user_id) values(1,100);
insert into bookmarks(bookmark_id,user_id) values(2,100);
insert into bookmarks(bookmark_id,user_id) values(3,100);
insert into bookmarks(bookmark_id,user_id) values(4,101);
insert into bookmarks(bookmark_id,user_id) values(5,102);
insert into bookmarks(bookmark_id,user_id) values(6,103);
insert into bookmarks(bookmark_id,user_id) values(7,103);
insert into bookmarks(bookmark_id,user_id) values(8,103);
insert into bookmarks(bookmark_id,user_id) values(9,103);
insert into bookmarks(bookmark_id,user_id) values(10,103);
commit;
Query:
SQL> select * from bookmarks;
BOOKMARK_ID USER_ID
----------- ----------
1 100
2 100
3 100
4 101
5 102
6 103
7 103
8 103
9 103
10 103
10 rows selected.
SQL> select user_id
2 from bookmarks
3 group by user_id
4 having count(*)<5;
USER_ID
----------
100
102
101
|
|
|
|
Re: Problem with GROUP BY [message #326899 is a reply to message #326859] |
Fri, 13 June 2008 00:39   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
@cnvegnix
Thanks for the test case but don't put solution only hint or clue as stated in OraFAQ Forum Guide, "Responding to Posts" section:
Quote: | When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, especially in the homework and newbies forums, it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.
|
Regards
Michel
|
|
|
|