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

Question

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!

JPV · Accepted Answer · 2021-03-20 20:41:13Z

4

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)

answered Mar 20, 2021 at 20:41

JPV

27.4k4 gold badges37 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JP Dan Over a year ago

That was exactly it! Thank you so much, JPV!

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related