Home » SQL & PL/SQL » SQL & PL/SQL » GroupBy (Oracle 9, Windows)
GroupBy [message #426691] Mon, 19 October 2009 00:46 Go to next message
panchpan
Messages: 34
Registered: October 2007
Member
Hello.

I have a table emp with fields name , id, skills. Consider one employee has 3 entries due to having 3 different skills - How can I populate only records in output with employees with 3 skills?
Thank you
Re: GroupBy [message #426695 is a reply to message #426691] Mon, 19 October 2009 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, no, maybe, you have precise your question.
Post a working Test case: create table and insert statements along with the result you want with these data.
Also always post your Oracle version with 4 decimals.

Regards
Michel

Re: GroupBy [message #426697 is a reply to message #426691] Mon, 19 October 2009 01:10 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Search for STRAGG.

By
Vamsi
Re: GroupBy [message #426701 is a reply to message #426695] Mon, 19 October 2009 01:25 Go to previous messageGo to next message
panchpan
Messages: 34
Registered: October 2007
Member
Detailed case:
Create table emp with fields name, id, skills - all are varchar.
Inserted below entries in table:
name id skills
A 1 S1
B 2 S2
A 1 S2
A 1 S3
C 3 S4

The expected output is:
name id skills
A 1 S1
A 1 S2
A 1 S3

Because, I wanted to extract employees who has 3 skills.

Hope, I could explain the question little better now.

thank you
Re: GroupBy [message #426707 is a reply to message #426701] Mon, 19 October 2009 01:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The requirement is:
Quote:
create table and insert statements along with the result you want with these data.


Use COUNT in its analytic form.

Regards
Michel
Re: GroupBy [message #426715 is a reply to message #426701] Mon, 19 October 2009 02:15 Go to previous messageGo to next message
panchpan
Messages: 34
Registered: October 2007
Member
Please find the CREATE/INSERT.

CREATE TABLE emp
(name char(50),
id int(1),
skills char(2));

INSERT INTO emp
VALUES ('A', 1 , 'S1');

INSERT INTO emp
VALUES ('B', 2 , 'S2');

INSERT INTO emp
VALUES ('A', 1 , 'S2');

INSERT INTO emp
VALUES ('A', 1 , 'S3');

INSERT INTO emp
VALUES ('C', 3 , 'S4');

Expected Result:
name id skills
A 1 S1
A 1 S2
A 1 S3
Re: GroupBy [message #426716 is a reply to message #426715] Mon, 19 October 2009 02:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Use COUNT in its analytic form.

Regards
Michel

[Updated on: Mon, 19 October 2009 02:18]

Report message to a moderator

Re: GroupBy [message #426724 is a reply to message #426715] Mon, 19 October 2009 03:02 Go to previous message
_jum
Messages: 508
Registered: February 2008
Senior Member
You can use GROUP BY and HAVING too:
SELECT * FROM emp
 WHERE ID IN
   (SELECT ID FROM emp
  GROUP BY ID
    HAVING COUNT(DISTINCT skills) = 3
   );

If there are only DISTINCT skills (NOT NULL) in Your table, simple use COUNT(*).
Previous Topic: improve performance
Next Topic: How sysdate works?
Goto Forum:
  


Current Time: Mon Dec 05 02:54:59 CST 2016

Total time taken to generate the page: 0.10128 seconds