Search code examples
sqloracle-databasegroup-bywhere-clausehaving

How can I show combined "article" which has the same amount of "bids"?

I want to show the combinations of "offers" that have the same number of "bids". The Both "OID" of the combination and the number of "bids" should be output.

I know the logic I want to show which article has the same bids in combination of other article, but I don't know how I can write it down. Some code can help me to understand this.

More information about the structure of the tables "offer" and "bid". Also a "Expected result" which is shown the result

enter image description here

I really want to learn SQL.

Solution

  • Try running this..

    SELECT temp1.OID as OID1,temp2.OID as OID2,temp1.count1 as numberOfBids
FROM 
    (SELECT t1.OID,COUNT(t1.BID) as count1
               FROM bid t1 group by t1.OID) temp1
JOIN 
   (SELECT t2.OID,COUNT(t2.BID) as count2
           FROM bid t2 group by t2.OID) temp2
ON temp1.count1= temp2.count2 
AND temp1.OID <> temp2.OID

    But it will give output as below table

    OID1    OID2    numberOfBids
1        2          2
1        5          2
2        5          2
5        1          2
5        2          2