I am trying to compare the buy rates from same vendorid based on % and get the sell rate if all buy values are within 2% of the minimum buyrate. I am adding 5% to that buyrate. if one buy value goes above 2% then the used row value would be zero. And if the max buy value and the min buy value is less than 2% in difference then the sell value would be (max buyrate 5%). please see the table below that I got from following select query.

SELECT distinct LOWER(vendors.tag), vendors.buyrate, ((buyrate *'0.05') buyrate) AS midrate, (select distinct
min(midrate)) AS sellrate, vendors.ship, vendorid,  
used FROM vendors WHERE vendors.ship IN (9,0)   
order by vendorid

My goal is to get the following results :

Using 5.1.73 mysql version. Any help would be appreciated. Thank you.

CodePudding user response：

So that you understand, MySQL can do almost anything but somethings anent't really the thing for MySQL

In general, you need to now what you want and then you have to thing aboput how do you get that information at every level

To understand that you need to understamd all subqueries and join that i made, to follow your idea.

CREATE TABLE vendors (
  vendors1 char(30) ,
  buyrate decimal(10,5),
  ship int(11) ,
  vendorid int(30) ,
  used int(11) 

);
INSERT INTO vendors ( vendors1, buyrate,  ship, vendorid, used) VALUES
('crna', 1.15, 9, 2, 11),
('crna', 1.17,  9, 2, 12),
('crna', 1.20,  9, 2, 13),
('prllc', 1.10,  9,3, 14),
('prllc', 1.16,  9, 3, 15),
('wmllc', 1.32,  9, 4, 16),
('wmllc', 1.33, 9, 4, 17),
('wmllc', 1.34,  9, 4, 18),
('wmllc', 1.36,  9, 4, 19);

SELECT
vendors1, buyrate,selrate,  ship, v2.vendorid, IF(FIND_IN_SET(buyrate ,sellunder),  used,0) as used
FROM vendors v2
INNER JOIN
(SELECT vendorid,GROUP_CONCAT(buyrate) sellunder, MAX(buyrate)  * 1.05 selrate
FROM
(SELECT v.vendorid, buyrate FROM vendors v JOIN 
(SELECT vendorid, MIN(buyrate) * 1.02  minbyrate FROM vendors GROUP BY vendorid) v1 
ON v.vendorid = v1.vendorid
WHERE v.buyrate < minbyrate) t2
GROUP BY vendorid) v3 ON v2.vendorid = v3.vendorid

vendors1 | buyrate |   selrate | ship | vendorid | used
:------- | ------: | --------: | ---: | -------: | ---:
crna     | 1.15000 | 1.2285000 |    9 |        2 |   11
crna     | 1.17000 | 1.2285000 |    9 |        2 |   12
crna     | 1.20000 | 1.2285000 |    9 |        2 |    0
prllc    | 1.10000 | 1.1550000 |    9 |        3 |   14
prllc    | 1.16000 | 1.1550000 |    9 |        3 |    0
wmllc    | 1.32000 | 1.4070000 |    9 |        4 |   16
wmllc    | 1.33000 | 1.4070000 |    9 |        4 |   17
wmllc    | 1.34000 | 1.4070000 |    9 |        4 |   18
wmllc    | 1.36000 | 1.4070000 |    9 |        4 |    0

db<>fiddle here