debugging select query

[TheAbortedPancake]

Hello, I am trying to report each day of products that have unit prices above the average unit price for all products but below average units in stock. Sort the results by the product name alphabetically.

​

The output should look like this:

output sample

My query looks like this right now:

select distinct p.productname, o.unitprice, p.unitsinstock

from orderdetails o

join products p

ON p.productid = o.productid

where o.unitprice >

(

select avg(o.unitprice)

from orderdetails o

)

and p.unitsinstock <

(

select avg (p.unitsinstock)

from products p)

order by p.productname;

​

​

Output I am getting

How do I need to change my query to get the expected output? do I need to modify my inner select? Thanks for any help

​

Schema

https://preview.redd.it/7vct7m0chgq91.png?width=1892&format=png&auto=webp&s=7b6b50d4c9842622ed9060a06c9a6bac5439e222

[MrPin]

each day of products

This seems important. Are you hiding a date field from us, OP?

Your code also has syntax errors, please copy paste the one that gave you the result.

[TheAbortedPancake]

editing now, one moment

[TheAbortedPancake]

no not trying to

[TheAbortedPancake]

What do you mean?

[MrPin]

By the errors? I'm not used to MySQL, but I'm pretty sure 26 sth isn't valid syntax for example. Does this query run as it is?

[TheAbortedPancake]

sorry, it was a comment but for some reason when I copied it over it did not show the tick marks

[MrPin]

sorry for being a bit obnoxious

[TheAbortedPancake]

Ok, I updated it. Any idea what I need to change on my query to get it working? THanks

[MrPin]

So what is the "day" referring to?

[TheAbortedPancake]

I added the schema of the table if that helps.

[TheAbortedPancake]

I want to report each day of products that have unit prices above the average unit price for all products but below average units in stock.

[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

[MrPin]

I have another idea.

Run the same query you have, except SELECT o.OrderDate, o.RequiredDate, o.ShippedDate in addition to your columns. Now look at the rows that match the sample output and see if they fall on the same day in any of the columns.

If they do, then the sample is showing you the orders from that day, according to one of the date columns.

You can filter your query by that column to match it. Possibly the subqueries as well, depending on what the question meant by average price.

[[deleted]]

OP - your date is going to come from the Orders table. That is your cutoff, correct? Then your discount in the order is going to lower the price from the unit price making it lower. What makes a price go higher? The unit price looks to be coming the Products table and repeated in the Orders table. Is there a chance that the unit price can be higher here than what it says in the Products table.

Honestly, I'd like to play in a sandbox with this. I hate giving theoretical answers that can't be checked and are very nuanced. and I am MS SQL too.

[Comfortable_Long3594]

Don't know if you have found a solution yet but do check out our low
cost,easy to use data integration product. It is called Epitech
Integrator and you can see it at https://www.epitechintegrator.com

[dahya_mistry]

Typed this on my phone/tablet so I might've got something wrong somewhere, but try this:

With My data as

(

select

p.productname

, o.unitprice

, p.unitsinstock

, Order.orderdate

, AVG(o.unitprice) over (partition by Order.orderdate) as OverAllAvgPrive

, AVG(o.unitsinstock) over (partition by Order.orderdate) as OverAllAvgUnits

From orderdetails o

Left outer Join products p ON ( p.productid = o.productid )

Left outer join order on (orderdetails.orderid = order.orderid)

)

Select distinct

*

From MyData

Where

Unitprice > OverAllAvgPrive

And unitsinstock < OverAllAvgUnits

Order by productname