Skip to Main Content

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!

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

Fran
welcome to OTN forums.

Please search before post:
9648502
2305368
2201373

When the issue will be solved, remember mark this question as answered to keep clean that forum.
900442
you may try and use fully qualified host names instead of the simple host name when trying to establish a connection from sql developer.

Regards,
Raj K.
Osama_Mustafa
Refer to
SQL Developer Shows "Connection Closed" Error When Running a Query [ID 1297692.1]
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,093 views