MySQL sort by calculated value of 2 rows

Question

I'm trying to create a MySQL statement that will sort by a value calculated within the statement itself. My tables look like this:

posts
+----+-----------+--------------+
| ID | post_type | post_content |
+----+-----------+--------------+
|  1 |      post |        Hello |
|  2 |      post |        world |
+----+-----------+--------------+

postmeta
+---------+----------+------------+
| post_id | meta_key | meta_value |
+---------+----------+------------+
|       1 |    price |         50 |
|       1 |   retail |        100 |
|       2 |    price |         60 |
|       2 |   retail |         90 |
+---------+----------+------------+

I'm trying to calculate a value called savings (.5 for ID=1, .3 for ID=2) then sort by it. This is what I have so far but I'm not sure how to do a calculation across 2 rows (everything I found is about calculating between columns).

SELECT wposts.*
FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
WHERE wposts.ID = wpostmeta.post_id
AND wpostmeta.meta_key = 'Price'
AND wposts.post_type = 'post'
ORDER BY wpostmeta.meta_value DESC

Thanks for your help!

Answer 1

Edit: I misinterpreted your sample output of .5 as meaning 50 cents, likewise for .3 = 30 = 90 - 60, instead of the percentage you'd get from (100 - 50) / 100 and (90 - 60) / 90. Maybe you will still find this helpful, but it doesn't answer the question asked.

SELECT wposts.ID,
  SUM(wpostmeta.meta_value * (CASE
    WHEN wpostmeta.meta_key = 'price' THEN -1
    ELSE 1)
  ) AS savings
FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
WHERE wposts.ID = wpostmeta.post_id
AND wposts.post_type = 'post'
GROUP BY wposts.ID;

The key is summing the meta_values but flipping the sign of price so you actually get retail minus price, grouped on each ID so the aggregate function SUM deals with each group independently. Whether it's smart to do all this logic here is a different question, however. :)

(You may have to tweak this syntax for MySQL.)

Answer 2

AFAIK You cannot calculate things "between rows" other than by using aggregate functions, which will not help you in this case.

Perhaps you can join your tables instead so you get one row:

SELECT wposts.*, pri.meta_value / ret.meta_value
FROM $wpdb->posts wposts
INNER JOIN $wpdb->postmeta pri
      ON  pri.post_id = wposts.ID
      AND pri.meta_key = 'price'
INNER JOIN $wpdb->postmeta ret
      ON  ret.post_id = wposts.ID
      AND ret.meta_key = 'retail'
WHERE wposts.post_type = 'post'
ORDER BY pri.meta_value / ret.meta_value

Tip: Never put more than one table in the FROM clause.

Answer 3

Here's a simple join that'll calculate the savings per post which assumes that the meta_value is a numeric data type.

select posts.id,
       (retail.meta_value - price.meta_value) * 1.0 / retail.meta_value as savings
  from posts,
       (select * from postmeta where meta_key = 'price') as price,
       (select * from postmeta where meta_key = 'retail') as retail
 where posts.id = price.post_id
   and posts.id = retail.post_id
   and posts.post_type = 'post'
 order by savings;


+----+---------+
| id | savings |
+----+---------+
|  1 | 0.50000 |
|  2 | 0.33333 |
+----+---------+