how can i sum rows that share a similar term

how can i sum rows that share a similar term

Problem Description:

I want to edit my query below, so that it combines rows that share a similar term (i.e. Love/ like/ dislike) together
Current query & results

SELECT values,
count(distinct uuid) as count
FROM
  `response_values` AS rv
left JOIN
  `responses_comprehensive` AS r
ON
  r.question_id=rv.qid
WHERE
  question_wording="campaign rating" and values not like '%missing%'

GROUP BY
 values

output:

DESIRED TABLE OUTPUT

thanks in advance

Solution – 1

Basically, you need to create categories for each end value in order to group the data later on.

To build those categories, a CASE statement combined with the LIKE operator will be enough.

I would add a new column to the "response_values" table with the category.

This will allow you to retain the original response and add the category to perform aggregations later on.

This query will solve the question:

SELECT curated_values,
count(distinct uuid) as count
FROM
  (SELECT *, CASE WHEN LOWER(term) LIKE '%love%' THEN "LOVE IT"
     WHEN LOWER(term) LIKE '%hate%' THEN "HATE IT"
     WHEN LOWER(term) LIKE '%neutral%' THEN "NEUTRAL"
     ELSE "UNKNOWN" END AS curated_values FROM `response_values`) AS rv
left JOIN
  `responses_comprehensive` AS r
ON
  r.question_id=rv.qid
WHERE
  question_wording="campaign rating" and values not like '%missing%'

GROUP BY
 curated_values

You can adjust the CASE clauses to your needs. I have added a LOWER function, for example, to catch all case variations.