Collate language codes into one combined locale code using CASE WHEN, and count the number of times the combined locale code occurs on a date

Question

Learning about CASE WHEN, and I've come across a use case when I see multiple locale codes in Analytics. This is a much simpler question that is much easier to answer and read, than my prior question that was posted.

Examples are: en-us (english US) en-au (english australia) en-br (english brazil) es-es (spanish spain) es-419 (spanish-latam) pt-br (portuguese brazil) pt-pt (portugal)

How do I aggregate these in BigQuery so that instead of counting distinct values, I can count the number of times where only the first two characters of the locale are found?

The second part of this question is: How do I structure my table in such a way so that I am able to plot these counts by date?

Currently, the output is: date:language_code:CombinedLocale

Link to example data table: https://docs.google.com/spreadsheets/d/1XZp1nhNZySWI39kKhb3ydYYIImmrfAMcGJDS6ASThqg/edit?usp=sharing

I've tried:

SELECT date, COUNT(language_code),
CASE 
    WHEN language_code like '%af%' THEN 'AF'
    WHEN language_code like '%en%' THEN 'EN'
    WHEN language_code like '%ar%' THEN 'AR'
    WHEN language_code like '%ba%' THEN 'BA'
ELSE "Others"
END AS CombinedLocale
FROM date_locales

And:

Select date, COUNT(language_code)
FROM date_locales
WHERE CASE 
WHEN language_code like '%af%' THEN 'AF'
WHEN language_code like '%en%' THEN 'EN'
WHEN language_code like '%ar%' THEN 'AR'
WHEN language_code like '%ba%' THEN 'BA'
ELSE "Others"
END

This is the working code I have:

SELECT date, language_code,
CASE 
    WHEN language_code like '%af%' THEN 'AF'
    WHEN language_code like '%en%' THEN 'EN'
    WHEN language_code like '%ar%' THEN 'AR'
    WHEN language_code like '%ba%' THEN 'BA'
ELSE "Others"
END AS CombinedLocale
FROM date_locales

I expected the results to display a count of CombinedLocale table over time as such:

    Jan AF 3
    JAN EN 5
    FEB AF 5
    FEB EN 6
    MAR EN 2
    MAR EN 3

but I get an error message stating: SELECT list expression references column date which is neither grouped nor aggregated (Line: 1, Column: 8)

I believe I will need to aggregate the dates into Months first? I am under the impression BigQuery integration w/ DataStudio will automatically aggregate the date column.

Gordon Linoff Gordon Linoff · Accepted Answer · 2019-05-30T17:30:11

Are you just looking for an aggregation query?

SELECT date,
       (CASE WHEN language_code like '%af%' THEN 'AF'
             WHEN language_code like '%en%' THEN 'EN'
             WHEN language_code like '%ar%' THEN 'AR'
             WHEN language_code like '%ba%' THEN 'BA'
             ELSE 'Others'
        END) AS CombinedLocale,
       COUNT(*)
FROM date_locales
GROUP BY date, CombinedLocale;

Collate language codes into one combined locale code using CASE WHEN, and count the number of times the combined locale code occurs on a date

2 Answers