CODEWITHSUNDEEP
sqlpostgresql
Polyphase29
Polyphase29

Reputation: 503

Comparing SELECT queries of a single table

I'm working with a single table of vehicles, consisting of about 2000-3000 rows and contains a vehicle make/model along with a status (there are many different statuses, I am concerned about 'T' and 'Y'). I want to create a query that will return the count of vehicles that have the same make/model as entries but different status. For example:

INSERT INTO my_db(make, model, status) VALUES ("BMW", "6-SERIES", "T")
INSERT INTO my_db(make, model, status) VALUES ("BMW", "6-SERIES", "Y")
INSERT INTO my_db(make, model, status) VALUES ("BMW", "6-SERIES", "Y")

I want my query to return "1" because we have a BMW 6-Series that has been matched with a T and Y at some point. How can I accomplish this? I have attempt to make two separate tables and do a join, and also tried a subselect, but I can't seem to get it working.

edit: I should clarify - I only want it adding to the count if make/model has an entry for both T and Y, not different statuses in general.

Upvotes: 1

Views: 32

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You can use count(*) and exists if you want to count rows:

select count(*)
from my_db d1
where exists (select 1
              from my_db d2
              where d2.make = d.make and d2.model = d.model and
                    d2.status <> d.status
             );

If you want to count make/model combinations, I would suggest two levels of aggregation:

select count(*)
from (select make, model
      from t
      group by make, model
      having min(status) <> max(status)
     ) mm;

Upvotes: 1

Related Questions