google bigquery SQL group by aggregate function

Question

In big-query standard SQL I get the error message:

"Error: Column 7 contains an aggregation function, which is not allowed in GROUP BY at [17:22]

where column 7 is defined in the select clause as follows:-

CASE WHEN MIN(transaction_dttm) = transaction_dttm THEN 'NEW' ELSE 'EXISTING' END AS Fcquisition_Flag

Please note that transaction_date is a timestamp.

Any suggested solutions appreciated.

Edit your question and show a simplified version of your query, along with sample data and desired results. — Gordon Linoff
I don't see anything wrong with your CASE expression. You should post the full query. — Tim Biegeleisen

Gordon Linoff Gordon Linoff · Accepted Answer · 2018-07-16T11:12:21

Perhaps you just want window function in a subquery:

SELECT . . .
FROM (SELECT . . . ,
             (CASE WHEN transaction_dttm = MIN(transaction_dttm) OVER (PARTITION BY ?)
                   THEN 'NEW' ELSE 'EXISTING'
              END) AS Fcquisition_Flag
      FROM . . . 
     ) t
GROUP BY 1, 2, 3, 4, 5, 6, 7

It is unclear what you want in the PARTITION BY.

google bigquery SQL group by aggregate function

1 Answers