Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Merging two select statements (one using Count(*))

618026Jul 9 2008 — edited Jul 9 2008
Hi!

Given the table SALESMEETING_CUSTOMERLIST:
SALESMEETINGID------------CUSTOMERID
10000-------------------------------20001010
10001-------------------------------20002020
10001-------------------------------20001010
10001-------------------------------20007070
10002-------------------------------20003030
10003-------------------------------20001010
10004-------------------------------20002020
10004-------------------------------20005050
10005-------------------------------20006060


I'm using the following statement to select all CUSTOMERID's which occur more than once in a SALESMEETING_CUSTOMERLIST:

SELECT CUSTOMERID, COUNT(*) TotalCount
FROM SALESMEETING_CUSTOMERLIST
GROUP BY CUSTOMERID
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC

This gives me the following output:
CUSTOMERID------------TOTALCOUNT
20001010------------------ 3
20002020------------------ 2

Then I have the following script in which I insert the found CUSTOMERID's to:

SELECT * FROM SALESMEETING_CUSTOMERLIST
WHERE SALESMEETINGID IN(SELECT SALESMEETINGID
FROM SALESMEETING_CUSTOMERLIST
WHERE CUSTOMERID IN(20001010,20002020))

This gives me the following output:
SALESMEETINGID------------CUSTOMERID
10000-------------------------------20001010
10001-------------------------------20002020
10001-------------------------------20001010
10001-------------------------------20007070
10003-------------------------------20001010
10004-------------------------------20002020
10004-------------------------------20005050

(20003030 and 20006060 are omitted since these are the only customers who doesn't occure more than once in SALESMEETING_CUSTOMERLIST or is in the same salesmeeting as a customer who in turn is present more than once in SALESMEETING_CUSTOMERLIST)


So, I want to write this in ONE script so I don't have to go through these steps every time. Basically I want to merge the two scripts above in to one =)

Any ideas on how to do this?

Thanks!

/Björn

Comments

Frank Kulash

Hi, Björn,

In your second query, you use IN twice. The second time, the argument is a comma-delimited list, but you can use a sub-query just like you did in the first IN:

SELECT * FROM SALESMEETING_CUSTOMERLIST 
WHERE SALESMEETINGID IN
    (   -- Begin sub-query of salesmeetingid's wanted
    SELECT  SALESMEETINGID 
    FROM    SALESMEETING_CUSTOMERLIST 
    WHERE   CUSTOMERID IN
        (   -- Begin IN subquery of customerid's wanted
        SELECT    CUSTOMERID
        FROM      SALESMEETING_CUSTOMERLIST
        GROUP BY  CUSTOMERID
        HAVING    COUNT(*) > 1
        )   -- End sub-query of customerid's wanted
    )   -- End sub-query of salesmeetingid's wanted
;
Aketi Jyuuzou 
create table SALESMEETING_CUSTOMERLIST(SALESMEETINGID,CUSTOMERID) as
select 10000,20001010 from dual union
select 10001,20002020 from dual union
select 10001,20001010 from dual union
select 10001,20007070 from dual union
select 10002,20003030 from dual union
select 10003,20001010 from dual union
select 10004,20002020 from dual union
select 10004,20005050 from dual union
select 10005,20006060 from dual;

select SALESMEETINGID,CUSTOMERID
from (select SALESMEETINGID,CUSTOMERID,
      max(cnt) over(partition by SALESMEETINGID) as maxCnt
      from (select SALESMEETINGID,CUSTOMERID,
            count(*) over(partition by CUSTOMERID) as cnt
              from SALESMEETING_CUSTOMERLIST))
where maxCnt >1;

SALESMEETINGID  CUSTOMERID
--------------  ----------
         10000    20001010
         10001    20001010
         10001    20002020
         10001    20007070
         10003    20001010
         10004    20005050
         10004    20002020
618026
Hi Frank,

Thanks, I figured it was something like that but just couldn't get it to work =)

Again, thank you!

/Björn
1 - 3
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 6 2008
Added on Jul 9 2008
3 comments
4,080 views