Home » SQL & PL/SQL » SQL & PL/SQL » Problem with GROUP BY (Windows)
Problem with GROUP BY [message #326802] Thu, 12 June 2008 11:31 Go to next message
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 #326812 is a reply to message #326802] Thu, 12 June 2008 12:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Post why you tried
2/ please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
3/ Always post your Oracle version (4 decimals).
4/ ost a test case: create table and insert statements.

Regards
Michel
Re: Problem with GROUP BY [message #326859 is a reply to message #326802] Thu, 12 June 2008 20:56 Go to previous messageGo to next message
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 #326897 is a reply to message #326812] Fri, 13 June 2008 00:37 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Michel Cadot wrote on Thu, 12 June 2008 19:12
1/ Post why you tried

./fa/1600/0/

Re: Problem with GROUP BY [message #326899 is a reply to message #326859] Fri, 13 June 2008 00:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Problem with GROUP BY [message #326900 is a reply to message #326897] Fri, 13 June 2008 00:42 Go to previous message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@Frank, Laughing

Of course it was "what" but "why" is also a good question in Surrealism world.

Regards
Michel
Previous Topic: execute a sql file within sql?
Next Topic: Odd Select Query
Goto Forum:
  


Current Time: Fri Dec 02 12:09:38 CST 2016

Total time taken to generate the page: 0.08037 seconds