Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

How to count number of distinct pairs per partition in SQL?

I want to get the # of distinct pair combinations per level in my table.

Sample Data:

level       group_no    item_no
oiegown     1           1
oiegown     1           2
oiegown     1           2
oiegown     1           3
oiegown     2           1
wefwefw     1           1
wefwefw     2           2

My Attempt:

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

Wanted to do something like

COUNT(DISTINCT group_no, item_no) 
  OVER (PARTITION BY level)
AS item_count

Expected Output:

level       item_count
oiegown     4           
wefwefw     2           

But seems that COUNT() only accepts one argument in BigQuery. How can I modify my query to get my desired result?

>Solution :

You can join both column into one Text, and count the concatenated text

COUNT(DISTINCT CONCAT(group_no, item_no)) 
  OVER (PARTITION BY level)
AS item_count
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading