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

618026

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

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