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
deepakdot
Messages: 89
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 messageGo to next message
deepakdot
Messages: 89
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 messageGo to next message
mathguy
Messages: 106
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 messageGo to next message
Solomon Yakobson
Messages: 3269
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
deepakdot
Messages: 89
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

Previous Topic: How can I speed this query up?
Next Topic: Join purchases to customers
Goto Forum:
  


Current Time: Thu Mar 28 17:37:34 CDT 2024