CODEWITHSUNDEEP
google-bigquery
Alina
Alina

Reputation: 37

Counting and presenting only duplicates

So I have a table like this:

Name | Fruit | price
----------------------
Effy |   Apple| 8

Effy |   Banana | 9

Alam |   Apple | 8

Alam |   Banana | 10

Boji |   Apple | 11

Suppose I want to get rid only from the Names that has one value (like Boji). How can I do it in big Query?

I thought to add HAVING clause and to add Having count(*) > 2, since this table is already grouped by, but i's no working the way I want.

Select Distinct name, fruit, price from fruit_db
group by name, fruit

output:

Name | Fruit | price
----------------------
Effy |   Apple| 8

Effy |   Banana | 9

Alam |   Apple | 8

Alam |   Banana | 10

Upvotes: 0

Views: 30

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

Try below

select * except(qualified) from (
  select *, 
    count(1) over(partition by name) > 1 qualified
  from `project.dataset.table`
)
where qualified

if applied to sample data in your question - output is

enter image description here

Upvotes: 2

Related Questions