Forum Stats

  • 3,734,031 Users
  • 2,246,861 Discussions
  • 7,857,002 Comments

Discussions

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

618026
618026 Member Posts: 44
edited July 2008 in SQL & PL/SQL
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
    Frank Kulash Member, Moderator Posts: 40,497 Red Diamond
    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
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited July 2008
    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
    618026 Member Posts: 44
    Hi Frank,

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

    Again, thank you!

    /Björn
This discussion has been closed.