CODEWITHSUNDEEP
sqlgoogle-sheetsgoogle-sheets-formula
JP Dan
JP Dan

Reputation: 11

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

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!

Upvotes: 1

Views: 5893

Answers (1)

JPV
JPV

Reputation: 27262

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)

Upvotes: 4

Related Questions