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)
