CODEWITHSUNDEEP
sqlgoogle-bigquery
bli00
bli00

Reputation: 2787

BigQuery GROUP BY ... HAVING asks for other columns to be grouped

Running something like this:

SELECT user_id, username FROM `table` GROUP BY user_id HAVING COUNT(user_id) = 1

But BQ console complains that username is neither grouped nor aggregated. I'm looking at this post that explains how to remove rows that appears more than once. I'm assuming this error message is because there's no primary key or uniques in BQ? How can I get around this? I just want to eliminate repeated rows by user_id.

Upvotes: 0

Views: 1187

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

If you want one row per user_id, you can just use an aggregation function such as:

SELECT user_id, MAX(username) as username
FROM `table`
GROUP BY user_id
HAVING COUNT(user_id) = 1;

However, I might suggest using QUALIFY instead:

select t.*
from table t
where 1=1
qualify count(*) over (partition by user_id) = 1;

Upvotes: 0

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

I just want to eliminate repeated rows by user_id.

below should do

SELECT user_id, ANY_VALUE(username) as username
FROM `table`
GROUP BY user_id

Upvotes: 2

Related Questions