Dear all,
I have the following query. When executed in MySQL I get an error on the
first sub-select, and I can't find why:
select slbc.stockcode, s.description, slp.qtyinstock, smax.pluqty,
(slp.qtyinstock / smax.pluqty)
from stocklevelbc slbc, stock s, stocklevel slp, stock smax,
stocklevelbc slbcmax
where slbc.stockcode = s.stockcode
and slbc.restcode = "NIC-PAG-1"
and slp.plu = s.plu
and slbc.restcode = slp.restcode
and smax.stockcode=slbcmax.stockcode
and slbcmax.stocklevelid in
(select z.stocklevelid from stocklevelbc
z, stock ss where z.restcode=slbc.restcode and z.stockcode in (select
sss.stockcode from stock sss
where sss.plu = s.plu) order by
qtytransfered desc)
The tables in use are:
STOCK
-----------
StockCode varchar(30)
Description varchar(100)
PLU varchar(20)
PLUQty decimal(10,4)
STOCKLEVELBC
--------------------------
StocklevelID int(11) auto_increment
RestCode varchar(20)
StockCode varchar(30)
QtyInStock decimal(10,4)
QtyTransfered decimal(10,4)
STOCKLEVEL
----------------------
StocklevelID int(11) auto_increment
RestCode varchar(20)
PLU varchar(20)
QtyInStock decimal(10,4)
QtyTransfered decimal(10,4)
The idea is that there are several stock codes that are the same
product, but different packagings. All "same products" have the SAME
*PLU* code, so that they are "grouped" together. When sales are made
(these are for a restaurant), the sale is done from the PLU and NOT from
the stockcode, because products are purchased in bottles, but sold in
glasses, so we cannot know which bottle was opened to sell a glass of
whisky for example.
So I have the QtyInStock in the STOCKLEVEL table showing the stocklevel
for each PLU.
Now, I want to know how many BOTTLES this QtyInStock refers to, by
dividing the QtyInStock by the number of the PLUQty for the "MOST
PURCHASED TYPE OF PACKAGING/BOTTLE"
For example:
Carlsberg beer (small bottle - 33CL) - Bottles transferred = 500 bottles
Carlsberg beer (large bottle - 63CL) - Bottles transferred = 200 bottles
Carlsberg beer (common PLU) - QtyInStock = 20,000CL
Results should be:
=> Most popular packaging - Small Bottle 33CL
=> QtyInStock represented as a number of most popular packaging in stock
= 20,000 / 33 = 606.06
Any ideas VERY much appreciated....
Regards,
Christos Vorkas
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
