CODEWITHSUNDEEP
mysqlsql
David542
David542

Reputation: 110512

How to do a price comparison for a product in SQL

I have the following data:

product_id       store           price
1                itunes          8.99
1                google          9.99
1                amazon          10.00
2                itunes          10.00
2                google          4.99

How would I do a query where I get all product_ids where the price is higher on iTunes than it is in Google. In the above example, it would be:

product_id      itunes      google
2               10.00       4.99

So far I have an aggregation query to do this, but am getting caught up in the price comparison part:

SELECT 
  product_id, 
  GROUP_CONCAT(case when platform_type_id='itunes' then price end) itunes,
  GROUP_CONCAT(case when platform_type_id='google' then price end) google
 FROM 
  table GROUP BY product_id

The query is incomplete, but was the direction I was going. What would be the correct way to do this?

Upvotes: 1

Views: 553

Answers (3)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

You can use a comparison in having.

SELECT 
  product_id, 
  MAX(CASE WHEN store='itunes' THEN PRICE END) as itunes,
  MAX(CASE WHEN store='google' THEN PRICE END) as google
FROM table 
GROUP BY product_id
HAVING MAX(CASE WHEN store='itunes' THEN PRICE END) >  MAX(CASE WHEN store='google' THEN PRICE END)

EDIT:

You can simplify the HAVING clause to:

HAVING itunes > google

Upvotes: 1

sticky bit
sticky bit

Reputation: 37487

Another possible solution would use an INNER JOIN connecting each iTunes product with the corresponding Google product where Google is cheaper.

SELECT ti.product_id,
       ti.price itunes,
       tg.price google
       FROM elbat ti
            INNER JOIN elbat tg
                       ON tg.product_id = ti.product_id
                          AND tg.price < ti.price
       WHERE ti.store = 'itunes'
             AND tg.store = 'google';

Upvotes: 3

Denis
Denis

Reputation: 1565

In Postgres though:

with itunes as (select productid, price from table_ where store='itunes'),
google as (select productid,price table_ where store='google')

Select itunes.productid, itunes.price as "price", google as "price"
From itunes,google
Where itunes.price>google.price

Upvotes: -1

Related Questions