0

I have a query where i want to filter a list of results but base it on a percentage between 2 values.

I have two databases 'Amazon' and 'Zavvi' in both of these databases i have the same products and how much they sell for and was filtering the results using the following

SELECT amazon.price AS amazon_price, zavvi.price AS zavvi_price 
FROM amazon.price, zavvi.price 
WHERE amazon.price > zavvi.price

However i want to work out the percentage of the two prices, and then display only records where are a percentage of that amount, but do it all within a query if possible.

at the moment i have to use the above query i have given an example too, and then within a loop do a IF statement which calculates the percentage for each record and then if it meets my needs add it to an array like so.

$myrow['percent'] = number_format((100/$myrow['amazon_price'])*$myrow['zavvi_price'],0);
        if ($myrow['percent'] >= 80) {
  • 4
    If you ask a question about queries, don't post PHP. Post your schema. Better yet, create an SQL fiddle with your schema defined for us to hack on. Really makes it easy to answer. I've already exceeded my "mock up other people's easily exported schema" quota for today :P – erik258 Jan 02 '15 at 22:50
  • 1
    `SELECT .... HAVING FORMAT(100/amazon_price)*zavvi_price, 0) > 80`? – Sean Jan 02 '15 at 22:54
  • @sean comment above looks good but you need to add it to the WHERE clause rather than the HAVING clause. You also don't need the format zavvi_price / amazon_price > 0.8 – Leo Jan 02 '15 at 23:32
  • Don't use comma-join syntax. It's so last year. – Strawberry Jan 02 '15 at 23:50
  • 1
    @Leo `HAVING` is necessary if referencing the aliases -> `amazon_price`/`zavvi_price`, `WHERE` would require the original column names -> `amazon.price`/`zavvi.price`. [`The alias is used as the expression's column name and can be used in GROUP BY, ORDER BY, or HAVING clauses...It is not permissible to refer to a column alias in a WHERE clause, because the column value might not yet be determined when the WHERE clause is executed.`](http://dev.mysql.com/doc/refman/5.0/en/select.html) see also http://stackoverflow.com/questions/200200/can-you-use-an-alias-in-the-where-clause-in-mysql – Sean Jan 03 '15 at 00:22
  • Thanks @Sean I learn something everyday. I didn't know that. I have been rewriting the equation in the where. that will save me time – Leo Jan 05 '15 at 19:44
  • http://stackoverflow.com/questions/2905292/where-vs-having the second answer by @fishdrowned is more complete and explains the performance implications. – Leo Jan 05 '15 at 20:00

2 Answers2

0

Please try the following query, which I hope should help:

SELECT 
    amazon.price AS amazon_price, 
    zavvi.price AS zavvi_price 
FROM 
    amazon, 
    zavvi 
WHERE 
    amazon.price > zavvi.price
    AND zavvi.price >= 0.8 * amazon.price

Condition AND zavvi.price >= 0.8 * amazon.price seems logically equal to the calculated value $myrow['percent'] >= 80

BTW I think that the part in original query FROM amazon.price, zavvi.price is a mistake.

UPDATED

I've edited the answer taking into account the right suggestion of colleagues about proper joining of the tables and the information provided in your comment to @Leo's answer. Below please find the INNER-JOINED version of the query:

SELECT 
    amazon.price AS amazon_price, 
    zavvi.price AS zavvi_price 
FROM 
    amazon 
    INNER JOIN zavvi 
        ON amazon.ean = zavvi.ean
WHERE 
    amazon.price > zavvi.price
    AND zavvi.price >= 0.8 * amazon.price

If you needed some information on joins, here you can find some help:

Community
  • 1
  • 1
0

you can also do the math inside

SELECT TOP 100 

    amazon.price AS amazon_price, 
    zavvi.price AS zavvi_price,
    zavvi.price / amazon.price AS zavvi_ratio 
FROM amazon.price, zavvi.price 

WHERE amazon.price > 0 AND 
    amazon.price > zavvi.price

HAVING zavvi_ratio > 0.8

ORDER BY zavvi_ratio DESC

P.S. I assume there is some kind of join here that you are excluding for brevity. 

FROM amazon INNER JOIN zavvi ON amazon.id = zavvi.id
Leo
  • 1,495
  • 23
  • 41
  • Hi Leo, i am still learning and my experience with MYSQL has been limited and i guess lazy; using PHP do to a lot of the filtering for me and realised this has made things very slow when dealing with large amounts of records and given me a kick up the back side to learn more about MYSQL. so in short i've not got a JOIN anywhere as I'm still trying to understand it. Thanks for your reply. –  Jan 02 '15 at 23:11
  • you need to join the tables some how otherwise it will not compare the item in amazon to the matching item in zavvi. Unless they are in the same table – Leo Jan 02 '15 at 23:24
  • the amazon.price greater than 0 is important to avoid divide by 0 errors. – Leo Jan 02 '15 at 23:30
  • Hi, yes sorry i had a "WHERE amazon.ean = zavvi.ean" in the query to match up the records. And thanks for pointing the amazon.price > 0 out as i spotted this my self also and made the change. :) –  Jan 02 '15 at 23:32