CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Pythonnewb
Pythonnewb

Reputation: 13

Cannot count on subquery and get proper results.Oracle SQL

I have two tables:

Advertisement(ano, newspaper, pno)

Property(pno, street, suburb, post, first_list, type, peid)

My goal is to list the properties that have been advertised in at least 2 different newspapers.

Currently I have tried these two queries:

SELECT P.pno, P.suburb, P.type
FROM (SELECT pno, newspaper
      FROM (SELECT DISTINCT pno, newspaper 
            FROM advertisement)
            GROUP BY pno, newspaper) SQ1, property P, advertisement A
WHERE P.pno = A.pno AND SQ1.pno = P.pno
GROUP BY P.pno, P.suburb, P.type
HAVING COUNT(SQ1.pno) > 1
;

and

SELECT P.pno, P.suburb, P.type
FROM (SELECT DISTINCT pno
      FROM  advertisement 
      GROUP BY pno
      HAVING COUNT(*) > 1) A1
      LEFT JOIN property P ON P.pno = A1.pno;

which gives the following results:

enter image description here

So the problem with my queries are they are not accounting for properties advertised in the same newspaper twice. They both return every property advertised more than once. The rogue property here is pno = 40. It is listed twice, but in the same newspaper.

When I run the subquery it gives me the pno without duplicates.

Example:

This is the advertisement table.

enter image description here

and this is the result of the subquery.

enter image description here

So the goal is to count only the pno in the subquery that exist more than once, > 1 at this point. I have spent more time on this one issue than I care to admit. Any help would be greatly appreciated.

Upvotes: 1

Views: 41

Answers (1)

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

You can use below query which is using aggregation with distinct clause -

SELECT P.pno, P.street, P.suburb, P.post, P.first_list, P.type, P.peid
  FROM Advertisement AD
  JOIN Property P ON P.pno = AD.pno
 GROUP BY P.pno, P.street, P.suburb, P.post, P.first_list, P.type, P.peid
HAVING COUNT(DISTINCT AD.newspaper) > 1

Upvotes: 1

Related Questions