Page 1 of 1

Finding a quantity with the lowest price

Posted: Tue Oct 18, 2005 12:04 pm
by kendall
hello,

i have a database in which products have several prices based on quanty

i am trying to build a query that will be able to select products whose lowest qty price is within a price range

the below is the code

Code: Select all

SELECT DISTINCTROW cost.ProductID, ProductName, MIN( cost.QTY ) , cost.Price, cost.PriceOption, Description
FROM product_prices AS cost
LEFT JOIN products AS prdct ON cost.ProductID = prdct.ProductID
GROUP BY cost.ProductID
HAVING MIN( cost.QTY ) AND cost.Price
BETWEEN 0.00
AND 3.00
however this apparantly doesnt work. i know i am close to a solution but i just want a push in a direction

any pushers?

Kendall

Posted: Tue Oct 18, 2005 4:36 pm
by timvw

Code: Select all

SELECT MIN(cost.Price) AS price, ProductName
FROM product_prices AS cost
INNER JOIN products USNIG ProductID
GROUP BY cost.ProductID
WHERE cost.Price between 0.00 AND 3.00

Posted: Tue Oct 18, 2005 5:20 pm
by deltawing
USING not USNIG. Obviously a typo.

Posted: Tue Oct 18, 2005 6:00 pm
by timvw
A bit later i realise that the problem is probably that you can only select columns that you are mentionning in your group by statement and/or results of a set operator on other columns... I don't know how well mysql enforces this...

Basically, if you also want to get product.whatever, you need to add it to your groupby clause...

SELECT products BETWEEN price range

Posted: Wed Oct 19, 2005 8:40 am
by kendall
timvw,

thanks for your offering you are a bit off but i think u have given me an idea.
Firts of all i do not want to get the MIN(cost.Price) but in fact i need to run the expression on the MIN(cost.QTY) of each product whose price is in the BETWEEN range.
I thought my query woul have first find the lowest qty of each product THEN match the price of the lowest qty to the range but this is not what it is happening. instead it is doing each expression independantly rather than matching the lowest qty then matching the price for each lowest qty.

see this thread here for an example of the table's layout
[url ='viewtopic.php?t=37380&highlight=']thread[/url]

Posted: Wed Oct 19, 2005 4:51 pm
by timvw
i am trying to build a query that will be able to select products whose lowest qty price is within a price range

Code: Select all

SELECT ProductID
FROM product_prices
GROUP BY ProductID
WHERE MIN(Price) BETWEEN 0.00 AND 3.00
And if you want some more product info:

Code: Select all

SELECT *
FROM products
WHERE ProductID IN (
  SELECT ProductID 
  FROM product_prices
  GROUP BY ProductID 
  WHERE MIN(Price) BETWEEN 0.00 AND 3.00 
)

Posted: Thu Oct 20, 2005 10:52 am
by kendall
timw,

your off a bit. lett me give you and example
This is my table
ID ProductID PriceOption QTY Price
1 PID3 1 Colour print 10 2.25
2 PID3 1 Colour print 20 1.00
3 PID3 2 Colour print 10 4.25
4 PID3 2 Colour print 20 1.50
5 PID6 1 Colour print 50 1.15
6 PID6 1 Colour print 60 1.00
7 PID6 1 Colour print 100 10.75
8 PID6 2 Colour print 50 2.15
9 PID6 2 Colour print 60 0.75
10 PID6 2 Colour print 70 0.24
13 PID7 Full Colour print 50 12.46
14 PID7 Full Colour print 100 8.24
15 PID8 Large 10 123.24
16 PID8 Large 20 100.00
17 PID8 Medium 10 84.10
18 PID8 Sml 50 90.24
You will notice that products have different quantities that have different prices
the lower the quantity the higher the price
now from your code
i am going to pull the products that have a min price between the range
this is not what i want
i need
to get the products whose min(qty)'s price is between the price range
now my last code works....but the katch is that the data as to be entered in sequenceial order
thus
ID ProductID PriceOption QTY Price
1 PID3 1 Colour print 20 1.25
2 PID3 1 Colour print 10 4.00
3 PID3 2 Colour print 20 1.25
4 PID3 2 Colour print 10 4.50
would put PID3 within the range 0 and 3...which is WRONG it should be within the "BETWEEN 3 AND 5" because 1 Colour Print 10 4.00
Hope this explains my case :idea: