By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
| version() |
|---|
| 8.0.25 |
create table table_acc( id int, item VARCHAR(255) );
insert into table_acc values ( 1, 'pizza' );
insert into table_acc values ( 1, 'panini' );
insert into table_acc values ( 1, 'pizza' );
insert into table_acc values ( 1, 'panini' );
insert into table_acc values ( 2, 'pizza' );
insert into table_acc values ( 3, 'panini' );
insert into table_acc values ( 4, 'pizza' );
insert into table_acc values ( 4, 'potato' );
select * from table_acc
| id | item |
|---|---|
| 1 | pizza |
| 1 | panini |
| 1 | pizza |
| 1 | panini |
| 2 | pizza |
| 3 | panini |
| 4 | pizza |
| 4 | potato |
with content as (
select
id,
group_concat(item," ") as food
from
table_acc
group by 1)
select
content.id,
sum(case when content.food like '%iz%'
and content.food like '%nin%'
then 1 else 0 end) as count_pizza_and_panini,
sum(case when content.food like '%iz%'
and content.food like '%tat%'
then 1 else 0 end) as count_pizza_and_potato
from content
group by 1
| id | count_pizza_and_panini | count_pizza_and_potato |
|---|---|---|
| 1 | 1 | 0 |
| 2 | 0 | 0 |
| 3 | 0 | 0 |
| 4 | 0 | 1 |