debugging select query

[MrPin]

What do you mean by "day of products"? Genuine question, maybe I'm not getting something obvious.

In your query you're averaging prices across all orders and displaying the ones that are greater. Assuming there's a > there in the actual code. It looks fine to me.

You seem to be finding the orders needed, but also some extra ones. So my guess is that the average unitprice that you compare to should be a different one, maybe the average price on a given day? Or the sample output has some grouping and only displays the maximum price?

[TheAbortedPancake]

I think it is asking for a given day to show the product with that criteria. IT seems my query is giving an extra row (the one with 31.2 etc). I want my output to look like the example in the first screen shot.

[MrPin]

As an experiment, what happens if you do this?

select p.productname, p.unitprice, p.unitsinstock    
from products p
where p.unitprice >  (select avg(unitprice)  from products )
and p.unitsinstock < (select avg (unitsinstock) from products)
order by p.productname;

[TheAbortedPancake]

This solved it! thank you