CODEWITHSUNDEEP
sqldatabasepostgresqlselectgroup-by
dmc94
dmc94

Reputation: 536

How can I query based on a difference in a column?

I was wondering how can I query a column to check if there is a delta in that column. Below I have a table of cars and their prices and I was wondering if there was a way to write a query where I see the cars that match in title but differ in price. Below is what my table looks like and the current query I am using.

select titles, price from cars_scrape where titles LIKE ANY(ARRAY['% Ford Flex', 'Ford Flex', '% Ford Flex %', 'Ford Flex %']);

              titles               | price 
-----------------------------------+-------
 2009 Ford Flex 4dr Limited AWD    | 5995
 2011 Ford Flex Limited w/EcoBoost | 9500
 2016 Ford Flex SEL                | 21178
 2016 Ford Flex SEL                | 21637
(4 rows)

I have not been able to write a query to return just the 2016 Ford Flex SEL Row based on the price difference. Any help would be greatly appreciated.

Upvotes: 0

Views: 28

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270081

I would be inclined to use exists:

select cs.*
from cars_scrape cs
where exists (select 1
              from cars_scrape cs2
              where cs2.title = cs.title and cs2.price <> cs.price
             )
order by cs.title;

You can include your where clause in the outer query (that is part of your query but not mentioned in the question).

Upvotes: 0

GMB
GMB

Reputation: 222512

You can group by titles, and use a having clause to filter on groups that have more than one distinct price:

select titles
from cars_scrape 
group by titles
having min(price) <> max(price)

If you want to actually display the offending records, one option is to use window functions:

select *
from (
    select 
        cs.*, 
        min(price) over(partition by titles) min_price,
        max(price) over(partition by titles) max_price
    from cars_scrape cs
) t
where min_price <> max_price

Upvotes: 2

Related Questions