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

user_14445149 0
Viewed 509

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,

google-bigquery
2 Answers

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
)
user_52219440

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.

user_125312850
Related