CODEWITHSUNDEEP
google-bigquery
abby
abby

Reputation: 35

how can i sum rows that share a similar term

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:

values count
love it 5
love itlove it 1
hate it 50
hate ithate it 10
neutral 2
neutral neutral 2

DESIRED TABLE OUTPUT

values count
love it 6
hate it 60
neutral 4

thanks in advance

Upvotes: 0

Views: 52

Answers (1)

David Morales
David Morales

Reputation: 670

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.

Upvotes: 1

Related Questions