New answers tagged

0

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 ...


0

You can use HAVING to filter the columns you created: select subscriber_id, 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 from test_fields_table group by subscriber_id HAVING email LIKE '%com' AND ...


0

I got the answer to this question. https://dbfiddle.uk/?rdbms=oracle_18&fiddle=412a6e27a22741ee1c31eee4a3f2bf3a


2

Why do you not simply unpivot the hole table SELECT * FROM push_data_temp; ID_PK | ID | COL1 | COL2 ...


1

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, ...


0

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` FROM `cars_new` WHERE 1 AND `make` IN ('Chrysler', 'Ferrari', 'Citroen', 'Dodge') GROUP BY `make`, `model`) `temp_table` where 1 GROUP BY `temp_table`.make ...


0

You have to GROUP BY both make and model, to get the count SELECT `make`,`model`, COUNT(*) as `countmakemodel` FROM `cars_new` WHERE `make` IN ('Chrysler', 'Ferrari', 'Citroen', 'Dodge') GROUP BY `make`,`model`


Top 50 recent answers are included