Home » SQL & PL/SQL » SQL & PL/SQL » Group by matching records (Oracle 10g)
Group by matching records [message #572997] Wed, 19 December 2012 06:34 Go to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

I have one table employee where there are 4 fields ,emp_code,emp_locn,emp_job_code,emp_job_desc the problem is i am trying to prepare a group reports based on location and emp_job_code there is a duplication of data in the emp_job_desc , For example there is a job_code E2 Which has two different job_descriptions for two different employees like E2-PAINTER-SPRAY, E2- PAINTER -SPRAY, another example is E1-rigger , E2-RIGGER and so on.Is there a method to match them together as one description.

Please refer the attachment.
Re: Group by matching records [message #572998 is a reply to message #572997] Wed, 19 December 2012 06:53 Go to previous messageGo to next message
Littlefoot
Messages: 19636
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'd expect someone (who is a member of this forum for 3.5 years) to know how to properly ask a question.
Re: Group by matching records [message #572999 is a reply to message #572997] Wed, 19 December 2012 06:58 Go to previous messageGo to next message
Michel Cadot
Messages: 59166
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How many times have we asked you to proper post (and read the documentation)?
I'm pretty sure we are closed to 500.
You do not deserve any help.

Regards
Michel
Re: Group by matching records [message #573015 is a reply to message #572999] Wed, 19 December 2012 21:56 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Dear Michel/Littlefoot, i am really sorry for posting like that as it was done in a hurry, please find below the table creation, inserts , output comming and desired output for better understanding.


CREATE TABLE emp_job (emp_code VARCHAR2(12),emp_job_code VARCHAR2(6),emp_job_desc VARCHAR2(2000),emp_locn_code VARCHAR2(6));

INSERT INTO emp_job VALUES ('1001', 'E2', 'ERECTOR', '0');

insert into emp_job values ('1002','E3','FOREMAN ERECTION','0');

insert into emp_job values ('1003','E3','FOREMAN-ERECTION','0');

insert into emp_job values ('1004','E4','PAINTER -SPRAY','0');

insert into emp_job values ('1005','E4','PAINTER-SPRAY','0');

insert into emp_job values ('1006','P1','CRANE OPERATOR JR.-OH','0');

insert into emp_job values ('1007','P1','CRANE OPERATOR OH.-JR','0');

commit;

select count(emp_code),emp_job_code,emp_job_desc from emp_job
group by emp_job_code,emp_job_desc


EMP_JOB_CODE EMP_JOB_DESC      TOT_CNT
E2           ERECTOR               1
E3           FOREMAN-ERECTION      1
P1           CRANE OPERATOR OH.-JR 1
P1           CRANE OPERATOR JR.-OH 1
E4           PAINTER -SPRAY        1
E3           FOREMAN ERECTION      1
E4           PAINTER-SPRAY         1


Desired output

EMP_JOB_CODE EMP_JOB_DESC      TOT_CNT
E2           ERECTOR               1
P1           CRANE OPERATOR OH.-JR 2
E3           FOREMAN ERECTION      2
E4           PAINTER-SPRAY         2

Re: Group by matching records [message #573028 is a reply to message #573015] Thu, 20 December 2012 01:21 Go to previous messageGo to next message
Littlefoot
Messages: 19636
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
SQL> select emp_job_code, max(emp_job_desc) emp_job_desc, count(*) cnt
  2  from emp_job
  3  group by emp_job_code, soundex(emp_job_desc)
  4  order by emp_job_code;

EMP_JO EMP_JOB_DESC                     CNT
------ ------------------------- ----------
E2     ERECTOR                            1
E3     FOREMAN-ERECTION                   2
E4     PAINTER-SPRAY                      2
P1     CRANE OPERATOR OH.-JR              2

SQL>
Re: Group by matching records [message #573029 is a reply to message #573015] Thu, 20 December 2012 01:24 Go to previous messageGo to next message
Michel Cadot
Messages: 59166
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i am really sorry for posting like that as it was done in a hurry


And you should then know that you have not faster answer as we don't answer without a test case.

Why "CRANE OPERATOR OH.-JR" and not "CRANE OPERATOR JR.-OH"?

Note once again you have a bad design.
Your emp table should ONLY refer to emp_job_code NOT to amp_job_desc which should in an other table.

Regards
Michel
Re: Group by matching records [message #573128 is a reply to message #573028] Fri, 21 December 2012 03:47 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

thanks very much littlefoot and michel, for the help and as michel said its a bad design to store the name of job titles into one table , i dont know for what purpose they did as we got this erp from a vendor , but there is a master for this job_titles also , hence i solved it by linking the title codes to this master.
Re: Group by matching records [message #573129 is a reply to message #573029] Fri, 21 December 2012 03:48 Go to previous message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

thanks michel, there is one master for that , and i solved it by linking this table to that master.
Previous Topic: Values to columns of another table
Next Topic: full outer join performance
Goto Forum:
  


Current Time: Tue Sep 23 00:16:57 CDT 2014

Total time taken to generate the page: 0.32280 seconds