Google Sheets Query Function. How can I get only Unique or Distinct Rows?

May 2024 ยท 2 minute read

I am trying to answer a question on a case using the Query function on Google Sheets and am stuck on a particular problem.

I need to get the total number of unique orders per year. I used the formula below and managed to get the total orders per year.

=QUERY(raw_data!$A$1:$U$9995, "select YEAR(C), COUNT(B) group by YEAR(C)", 1) 

Where column C is the date and B is the order_id.

The problem is that this returns a total of 9994 orders and includes duplicates of the same order. For example, if a customer purchased 3 different products, they would each be given a line in the database and would count as 3 of the 9994 orders. However, they all have the same order_id.

I need to get the number of unique orders per year. I know this number is 5009 since I did some manual research through Excel, but wanted to find that same total, separated by year, using the Query Function since this is a case to test my SQL Knowledge.

Is this possible? Does the Query Function have a way to get the count for unique order_ids? Thank you very much for your help!

1 Answer

See if this helps

 =QUERY(UNIQUE(raw_data!$B$1:$C$9995), "select YEAR(Col2), COUNT(Col1) where Col2 is not null group by YEAR(Col2)", 1) 
1

ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJobW9vYmqEeHyOoKaon5yaerS0xJ6rrGWhqrKzxYyfrKebpJ68r3nHqK5mm5Gjeqp5xp6rZqeeocZuwc2iqK6dXaS%2FbrDIrKuippOperO71qw%3D