CODEWITHSUNDEEP
sqloracle-database
Robben
Robben

Reputation: 463

Wrapping query to get a count greater than 3

SELECT ownerid, zip, vin, make, model FROM listing 
WHERE (ownerid, upper(make)) 
IN (SELECT dealership, attribute FROM ownerproduct WHERE productid = 'FRONT_LISTING')
AND new = 'Y'
ORDER BY DBMS_RANDOM.RANDOM;

My query produces a front_listing. As it stands now, it only produces it about 85% percent of the time, so I need to wrap my query to get a count of ownerid, make, model to be greater than 3, in order for my query results to produce a front_listing 100% of the time.

Edit:

So I need a query where they have at least more than 3 models associated with the ownerid and make. So, for example, an owner id might have a model amount that is less than 3 however that same model might have a different ownerid where it does have a count more than 3 thus why I need a count/amount of ownerid, make and model to be greater than 3.

Upvotes: 0

Views: 81

Answers (2)

David Faber
David Faber

Reputation: 12486

I'm not sure how efficient your query would be with this sort of WHERE clause:

 WHERE (ownerid, upper(make)) 
    IN (SELECT dealership, attribute FROM ownerproduct WHERE productid = 'FRONT_LISTING')

I would try something like this instead:

SELECT ownerid, zip, vin, make, model FROM (
    SELECT op.dealership AS ownerid, l.zip, l.vin, l.make, l.model
         , COUNT(*) OVER ( PARTITION BY op.dealership, l.make, l.model) cnt
      FROM ownerproduct op INNER JOIN listing l
        ON op.dealership = l.ownerid
       AND op.attribute = UPPER(l.make)
     WHERE op.productid = 'FRONT_LISTING'
       AND l.new = 'Y'
) WHERE cnt >= 3
 ORDER BY DBMS_RANDOM.VALUE;

P.S. The reason I used DBMS_RANDOM.VALUE instead of DBMS_RANDOM.RANDOM is that the latter is deprecated (since 11gR1).

Upvotes: 1

Matthew McPeak
Matthew McPeak

Reputation: 17924

Is this what you mean?

SELECT ownerid, zip, vin, make, model 
FROM 
(
SELECT ownerid, zip, vin, make, model, count(*) over (partition by ownerid, make, model) cnt FROM listing 
WHERE (ownerid, upper(make)) 
IN (SELECT dealership, attribute FROM ownerproduct WHERE productid = 'FRONT_LISTING')
AND new = 'Y'
)
WHERE cnt > 3
ORDER BY DBMS_RANDOM.RANDOM );

Upvotes: 2

Related Questions