Home » SQL & PL/SQL » SQL & PL/SQL » Group By (merged) (19C)
Group By (merged) [message #687650] |
Thu, 27 April 2023 05:27 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/da41024bca4935db557890be9c13b82c?s=64&d=mm&r=g) |
deepakdot
Messages: 91 Registered: July 2015
|
Member |
|
|
Hi
I have a requirement .
create table emp (EMPNO NUMBER(10), SAL number(10), JOB varchar2(20), DEPT number (10));
insert into emp values (1234, 3000, 'PRESIDENT', 30 );
insert into emp values (1234, 3000, 'PRESIDENT', 40 );
insert into emp values (5678, 4000, 'MANAGER', 20 );
COMMIT;
I want to return the EMPNO and DEPT, if EMPNO belong to more then one Dept , with some filters like SAL, GRADE Etc.
SQL> select empno from EMP where SAL > 1000 and JOB = 'PRESIDENT' group by EMPNO having count(*) > 1;
This will return me only One Row with EMPNO = 1234. But I want to return EMPNO and DEPT ( 2 Rows, I want to know what are dept this EMPNO belongs to).
what is the efficient way to write this sql. Please note, this table will have millions of rows. I can write this as below , but I have to add the filters for SAL and JOB multiple times, which may not be efficient.
SQL> select empno , DEPT from EMP where empno in (select empno from EMP where SAL > 2000 and JOB = 'PRESIDENT' group by EMPNO having count(*) > 1)
and SAL > 1000 and JOB = 'PRESIDENT';
Regards,
Deepak
|
|
|
Group By [message #687651 is a reply to message #687650] |
Thu, 27 April 2023 05:27 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/da41024bca4935db557890be9c13b82c?s=64&d=mm&r=g) |
deepakdot
Messages: 91 Registered: July 2015
|
Member |
|
|
Hi
I have a requirement .
create table emp (EMPNO NUMBER(10), SAL number(10), JOB varchar2(20), DEPT number (10));
insert into emp values (1234, 3000, 'PRESIDENT', 30 );
insert into emp values (1234, 3000, 'PRESIDENT', 40 );
insert into emp values (5678, 4000, 'MANAGER', 20 );
COMMIT;
I want to return the EMPNO and DEPT, if EMPNO belong to more then one Dept , with some filters like SAL, GRADE Etc.
SQL> select empno from EMP where SAL > 1000 and JOB = 'PRESIDENT' group by EMPNO having count(*) > 1;
This will return me only One Row with EMPNO = 1234. But I want to return EMPNO and DEPT ( 2 Rows, I want to know what are dept this EMPNO belongs to).
what is the efficient way to write this sql. Please note, this table will have millions of rows. I can write this as below , but I have to add the filters for SAL and JOB multiple times, which may not be efficient.
SQL> select empno , DEPT from EMP where empno in (select empno from EMP where SAL > 2000 and JOB = 'PRESIDENT' group by EMPNO having count(*) > 1)
and SAL > 1000 and JOB = 'PRESIDENT';
Regards,
Deepak
|
|
|
Re: Group By [message #687653 is a reply to message #687651] |
Thu, 27 April 2023 08:53 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/5db0aa55eb576006ce1bb00eee2e35d9?s=64&d=mm&r=g) |
mathguy
Messages: 108 Registered: January 2023
|
Senior Member |
|
|
I don't understand the question.
Employee 1234 appears in more than one department. That much is clear.
The output should select only employees with JOB = 'PRESIDENT' and SAL > 1000. This part is unclear. The DEPT is not uniquely determined by EMPNO. In your data, JOB and SAL are uniquely determined by EMPNO. Is that true in the real-life data? That is, you can't have the same EMPNO on different rows, with different JOB (or different SAL)?
If the answer is "correct, that can't happen - an employee may appear in more than one department, but their JOB and their SAL are unique to that employee" - then your data model violates Third Normal Form. This may cause numerous problems. The cure is to have a different table showing EMPNO, JOB, SAL - separate from the department assignments, which may be one-to-many (as in your sample data). In any case, even if you can't fix this, we need to know the answer so we can help with a query.
If the answer is "no - the same employee may have one JOB in department 30 and a different JOB in department 40" then you need to clarify the problem further. Which row or rows (if any) must be returned by your query, for that employee? For example, in your sample data, what if the second row (for employee 1234) has JOB='MANAGER' (and everything else unchanged)?
Assuming EMPNO completely determines both JOB and SAL, and only DEPT may be different: can there be employees who appear in the same department (say 30) more than once, that is on more than one row? Or do you have a unique constraint on (EMPNO, DEPT) to ensure that can't happen? Without this assumption, HAVING COUNT(*) > 1 will return employees who appear more than once in the table, but not necessarily in at least two distinct departments.
Finally, regarding your last attempt, where you feel that you need to add the filters more than once: In the subquery you have the condition SAL > 2000 (instead of comparing to 1000); is that a mistake? Assuming it must be 1000, then you don't need to repeat the filter in the outer query, why would that be necessary? That is already used in the subquery, if an employee has salary no more than 1000, he is guaranteed not to be returned by the subquery. Indeed, it is possible (even likely) that that approach is the most efficient (fastest) way to get the output you need.
|
|
|
Re: Group By [message #687654 is a reply to message #687653] |
Thu, 27 April 2023 11:37 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Solomon Yakobson
Messages: 3280 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Use analytic count or match recognize (if you are on newer Oracle version):
WITH T AS (
SELECT EMPNO,
DEPT,
COUNT(*) OVER(PARTITION BY EMPNO) CNT
FROM EMPX
WHERE SAL > 1000
AND JOB = 'PRESIDENT'
)
SELECT EMPNO,
DEPT
FROM T
WHERE CNT > 1
/
EMPNO DEPT
---------- ----------
1234 40
1234 30
SELECT EMPNO,
DEPT
FROM EMPX
MATCH_RECOGNIZE(
PARTITION BY EMPNO
ORDER BY JOB,
SAL
ALL ROWS PER MATCH
PATTERN(P{2,})
DEFINE P AS JOB = 'PRESIDENT' AND SAL > 1000
)
/
EMPNO DEPT
---------- ----------
1234 40
1234 30
SY.
|
|
|
Re: Group By [message #687658 is a reply to message #687653] |
Thu, 27 April 2023 23:54 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.gravatar.com/avatar/da41024bca4935db557890be9c13b82c?s=64&d=mm&r=g) |
deepakdot
Messages: 91 Registered: July 2015
|
Member |
|
|
Hi,
The real data model is Different. Real scenario is in a Supply Model. Here for example, I have put SAL and JOB just used as some filter. My Question is how to I retrieve this data in a Group by where I want to retrieve both EMPNO and DEPT, But group by can be only on EMPNO as I am checking the EMPNO have more than one Dept ( count > 1) .
Let me put this way. I have 3 rows for the same EMPNO.
create table emp (EMPNO NUMBER(10), SAL number(10), JOB varchar2(20), DEPT number (10));
insert into emp values (1234, 3000, 'J1', 20 );
insert into emp values (1234, 2000, 'J2', 30 );
insert into emp values (1234, 1500, 'J3', 40 );
COMMIT;
SQL> select empno from EMP where SAL > 1000 and JOB != 'J3' group by EMPNO having count(*) > 1;
This SQL gives me Only 1 row with EMPNO = 1234
But I want to Fetch both EMPNO and DEPT .
SQL> select empno , DEPT from EMP where empno in (select empno from EMP where SAL > 1000 and JOB != 'J3' group by EMPNO having count(*) > 1)
This will return all the 3 Rows. But this is not correct. I want to fetch where JOB != 'J3'. That why I need to add this filer also outside.
SQL> select empno , DEPT from EMP where empno in (select empno from EMP where SAL > 1000 and JOB != 'J3' group by EMPNO having count(*) > 1) and SAL > 1000 and JOB != 'J3';
This will give the 2 rows what I wanted to fetch. But the Filter "SAL > 1000 and JOB != 'J3'" , I need to add outside of the SQL as well.
The SQL Mentioned by Solomon is working for my scenario.
Regards
Deepak Samal
|
|
|
Goto Forum:
Current Time: Sat Sep 07 19:21:56 CDT 2024
|