CODEWITHSUNDEEP
sqlgoogle-bigquery
Simon Breton
Simon Breton

Reputation: 2876

How can I use GROUP BY with AGGREGATED column values

I'm learning SQL so I'm not sure about my question. I'm running the following query :

SELECT count(key), FORMAT_DATE("%Y-%m", DATE(min(created_date))) as First_Month
FROM `xxxxxxxx.xxxxxxx_xxxxx.xxxxxxxx` as table 
GROUP BY First_Month
ORDER BY First_Month

Big query returns me this error :

Error: Column First_Month contains an aggregation function, which is not allowed in GROUP BY at [3:10]

Basically I have a table with a list of account and payment. What I want to do is counting the number of accounts at their first month. Which is I think what we called a cohort analysis... but again I'm not sure.

I feel like there is something more complex beyond my question but I'm not able to express...

Upvotes: 0

Views: 329

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

Use a subquery. Presumably you intend something like this:

SELECT count(*), FORMAT_DATE("%Y-%m", DATE(min_created_date)) as First_Month
FROM (SELECT t.key, min(created_date) as min_created_date
      FROM `xxxxxxxx.xxxxxxx_xxxxx.xxxxxxxx` t
      GROUP BY t.key
     ) t 
GROUP BY First_Month
ORDER BY First_Month
LIMIT 1000;

You have a lot of history if you are expecting more than 1000 months of history.

Upvotes: 3

Related Questions