CODEWITHSUNDEEP
sqlapache-spark-sql
cget
cget

Reputation: 410

Windowing function COUNT() OVER (PARTITION BY) is not working for me

I have this data set:

enter image description here

I want to produce this:

enter image description here

Here is my query, it's not producing the desired result. I'm trying to count order_ids and partiton by country. What am I doing wrong?

Note that if I don't include order_id in the GROUP BY, I get the following error order_id is neither present in the group by, nor is it an aggregate function.

SELECT country,  
       COUNT(repeat_customer_id) repeat_customer,
       COUNT(order_id) OVER (PARTITION BY country) total_orders
FROM table
GROUP BY country, order_id

Upvotes: 0

Views: 164

Answers (1)

Stu
Stu

Reputation: 32619

You don't need a window function here, just normal aggregation grouping by country

SELECT country,  
       COUNT(repeat_customer_id) repeat_customer,
       COUNT(*) total_orders
FROM table
GROUP BY country;

Upvotes: 1

Related Questions