Here's the table I am dealing with -

Table Name: Filters

And I need to pull out count of unique IDs that repeat in both vendor and product. Need help!

Tried using AND clause for filter criteria, but did not work

my attempt below -

Select ID from Filters
Where Filter_type = 'vendor' AND Filter_type = 'product'

CodePudding user response：

The below query should give the desired result. Please check.

SELECT COUNT(DISTINCT id) FROM filters WHERE filter_type IN ('vendor', 'product');

If you are looking for a list of unique ID values, you can remove the count.

SELECT DISTINCT id FROM filters WHERE filter_type IN ('vendor', 'product');

CodePudding user response：

To get all the IDs whose FILTER_TYPE is vendor:

select ID from FILTERS where FILTER_TYPE = 'vendor'

To get all the IDs whose FILTER_TYPE is product:

select ID from FILTERS where FILTER_TYPE = 'product'

To get the IDs that have both vendor and product FILTER_TYPE:

select ID from FILTERS where FILTER_TYPE = 'vendor'
intersect
select ID from FILTERS where FILTER_TYPE = 'product'

You want to retrieve the IDs that have both vendor and product FILTER_TYPE as well as the count for each of those IDs.

select ID, count(*)
  from FILTERS
 where ID in (
               select ID from FILTERS where FILTER_TYPE = 'vendor'
               intersect
               select ID from FILTERS where FILTER_TYPE = 'product'
             )
 group by ID

Refer to this db<>fiddle

Note that you did not tag your RDBMS so the "fiddle" uses SQL Server, however INTERSECT should work with most RDBMS's.