I've created a query that will provide me all instances that have an '(AB)' in the field_name column:

select distinct field_name, count(distinct field_id) as field_amounts
from data.table
where field_name like '%(AB)%'
group by field_name
order by field_amounts desc;

This would produce a result like:

Now, I am looking to grab all the instances in the data set with the same exact field_names from this result but without the '(AB)' within the name. So, something like this:

Any help would be appreciated. Thank you.

EDIT: To clarify, there are instances in the data that have '(AB)' and ones that don't. I want all the field names with '(AB)' but then also the ones that have the same field_names but without the '(AB)'. For example, if the entire data set looks like this:

Then I would not want the field_name 'C' as there aren't any instances of 'C (AB)'. So, the new query I want would grab A, B, and D and count the instances.

CodePudding user response：

Try using the REPLACE function

CodePudding user response：

Just reverse the query by editting 3rd line as" where field_name not like '%(AB)%' "

CodePudding user response：

You can use aggregation and the condition in the HAVING clause:

SELECT TRIM(REPLACE(t.field_name, '(AB)', '')) field_name,
       COUNT(*) field_amounts
FROM tablename t
GROUP BY 1
HAVING SUM(t.field_name LIKE '%(AB)%') > 0;

See the demo.