CODEWITHSUNDEEP
google-bigquery
Caio César P. Ricciuti
Caio César P. Ricciuti

Reputation: 457

Bigquery SELECT * WHEN COUNT(DISTINCT value) does not work

I have a bigQuery table with 30+ columns and I want to SELECT * where session is unique.

I've been to almost all questions regarding this subject in StackOverflow but none helped me to achieve the expect result.

I've tried SELECT COUNT(DISTINCT session) FROM table.id but the problem is that returns only session column and I need the whole row.

Then I tried:

SELECT *
FROM `table.id`
WHERE session IN (
    SELECT session
    FROM `table.id`
    GROUP BY session
    HAVING COUNT(*) = 1
)

But it returns much less rows then SELECT COUNT(DISTINCT sessions)

So by logic I tried: SELECT *, COUNT(DISTINCT sessions) and SELECT * WHERE COUNT(DISTINCT sessions)

none works

Anyone can help? Thanks in advance and kind regards,

Upvotes: 2

Views: 1861

Answers (2)

Vijay
Vijay

Reputation: 888

You query seems alright with HAVING COUNT(*) = 1 as suggested by @Mikhail.
What wrong is that you are trying to match this result with SELECT COUNT(DISTINCT sessions). Note that DISTINCT is used to show distinct records including 1 record from duplicate too. On the other hand HAVING COUNT(*) = 1 is checking only records which are not duplicate.

For a simple example, if session has : 1, 1, 2, 3
DISTINCT will result in: 1, 2, 3
HAVING COUNT(*) = 1 will result in: 2, 3
hence the difference you see in both result.

Upvotes: 1

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

I want to SELECT * where session is unique ...

Use below instead - note use of = in COUNT(*) = 1

SELECT *
FROM `table.id`
WHERE session IN (
    SELECT session
    FROM `table.id`
    GROUP BY session
    HAVING COUNT(*) = 1
)

Upvotes: 2

Related Questions