mysql - Different value counts on same column using LIKE -

mysql - Different value counts on same column using LIKE -

i have database below

+------------+---------------------------------------+--------+ | sender | subject | day | +------------+---------------------------------------+--------+ | darshana | re: [dev] [platform] build error | mon | | dushan | (moldovadevdev-49) greg startup error | mon | +------------+---------------------------------------+--------+

i want result using above table. should check if subject contains given word add together 1 word column given day.

|day | "dev" | "startup"| +---------+------------+----------+ | mon | 1 | 2 | | fri | 0 | 3 |

i thought of using decode function couldn't expected result.

you can conditional aggregation:

select day, sum(subject '%dev%') dev, sum(subject '%startup%') startup table t grouping day;

mysql sql pivot