Your field_1 schema is likely to be slower than EAV or JSON. Wordpress, for example, uses an EAV schema pattern -- WP users are often grumbling on this and other forums about poor performance. JSON has pros and cons.
For performance, you must have the more common search columns in a single table with suitable datatypes. Less common search columns can be ...
You can use HAVING to filter the columns you created:
MAX(case when field_id = '1' then value else 0 end) as email,
MAX(case when field_id = '2' then value else 0 end) as first_name,
MAX(case when field_id = '3' then value else 0 end) as last_name
group by subscriber_id
HAVING email LIKE '%com'
This article was super helpful: https://postgresql.verite.pro/blog/2018/06/19/crosstab-pivot.html and the demo of particular use: https://dbfiddle.uk/?rdbms=postgres_11&fiddle=407a37686238bb3fbcbc4285d1705871
Unfortunately it turns out using a Pivot/CrossTab is much more complex than dynamically generating the original query with code:
SELECT employeeid, ...
So, I have found a solution to my problem. I am not sure if this is valid but it works for me.
SELECT `temp_table`.`make`, COUNT(temp_table.make) FROM (SELECT `make`, `model`, COUNT(`id`) as `tnncmake`
AND `make` IN ('Chrysler', 'Ferrari', 'Citroen', 'Dodge')
GROUP BY `make`, `model`) `temp_table` where 1 GROUP BY `temp_table`.make
You have to GROUP BY both make and model, to get the count
SELECT `make`,`model`, COUNT(*) as `countmakemodel`
WHERE `make` IN ('Chrysler', 'Ferrari', 'Citroen', 'Dodge')
GROUP BY `make`,`model`