SQL: Is it possible to 'group by' according to 'like' function's results?
Sure:
WITH Fruits AS ( SELECT CASE WHEN m.str LIKE '%APPLE%' THEN 'Apple' WHEN m.str LIKE '%ORANGE%' THEN 'Orange' END AS FruitType FROM MESA m WHERE m.str LIKE '%FRUIT%')SELECT FruitType, COUNT(*) FROM FruitsWHERE FruitType IN ('Apple', 'Orange')GROUP BY FruitType;
Another variant of David Markle answer:
SELECT fruit_name, count(1) as fruit_countFROM ( SELECT CASE WHEN m.str LIKE '%APPLE%' THEN 'Apple' WHEN m.str LIKE '%ORANGE%' THEN 'Orange' END as fruit_name FROM MESA m WHERE m.str LIKE '%FRUIT%' AND (m.str LIKE '%APPLE%' OR m.str LIKE '%ORANGE%'))GROUP BY fruit_name Same thing, but only 1 CASE required, which simplifies support ...