Cannot count on subquery and get proper results.Oracle SQL

Question

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:

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.

and this is the result of the subquery.

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.

Did you really try queries that begin with the keyword FROM? In Oracle SQL? And that didn't throw an error? Or are those not the queries you ran, but perhaps just parts of them? Please clarify. — user5683823
– user5683823, Commented Nov 29, 2020 at 16:24
@mathguy Was problem with code ``` the select statements were there just not displayed. — Pythonnewb
– Pythonnewb, Commented Nov 29, 2020 at 16:44
That is not true. Please click on the "edited ... ago" link, next to the "asked ... ago" marker at the bottom of your post. If you click on the "edited" link you can see the history of the post. There was no "select" clause in your earlier versions of the post, you just added that in your last edit. Please don't distort the truth here. — user5683823
– user5683823, Commented Nov 29, 2020 at 16:49
@mathguy Beleive what you want, but the original document had: ``` SELECT ...... Which needed to have the SELECT one line below the ``` — Pythonnewb
– Pythonnewb, Commented Nov 29, 2020 at 16:53
I don't believe what I want, I believe what I see. Did you review the history of your post, as I suggested? Do you see what YOU are claiming, anywhere there? Or are you saying that the history is somehow wrong, maybe someone changed your post in the history? (Note - no one, not even the site managers, can edit THAT.) — user5683823
– user5683823, Commented Nov 29, 2020 at 16:56

Ankit Bajpai · Accepted Answer · 2020-11-29 15:10:27Z

1

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

answered Nov 29, 2020 at 15:10

Ankit Bajpai

13.6k4 gold badges27 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pythonnewb Over a year ago

This worked beautifully. Thank you so much! Not sure why I didn't think of counting the newspapers instead of the pno.

Collectives™ on Stack Overflow

Cannot count on subquery and get proper results.Oracle SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related