sam123 sam123 - 6 months ago 13
SQL Question

categorize records in BigQuery

I am wondering if it is possible to categorize records in BigQuery.

For example;

number - site - visits
1-A-10
2-B-20


I would want to take Site A and B and create a group call "Cars". Can someone please tell me how this is done?

Answer

Below is the best I can think of based on your comments to your question
Hope it is what you asked for

SELECT
  CASE 
    WHEN site IN ('A', 'B') 
    THEN 'Cars' 
    ELSE 'Others' 
  END AS category,
  SUM(visits) AS visits
FROM
  (SELECT 1 AS number, 'A' AS site, 10 AS visits),
  (SELECT 2 AS number, 'B' AS site, 20 AS visits),
  (SELECT 3 AS number, 'C' AS site, 50 AS visits)
GROUP BY 1  

Added example for CASE WHEN statement

CASE 
  WHEN (Medium = "A" and source = "facebook.com" ) THEN 'Social' 
  WHEN (Medium = "B" and source = "facebook.com" ) THEN 'PaidSocial' 
END as Category