How to write proper partition by for this query

Question

We have data in this format.

item     club    dc     
1         2      100
1         3      100
1         4      200
1         5      200
1         6      200
1         7      300

2         3      100
2         4      200
2         5      200
2         6      200

What I need is, for each item dc wise count of all clubs. Response should be:

item dc   club_count
1    100   2
1    200   3
1    300   1
2    100   1
2    200   3

I tried with basic select item,dc, count(dc) over(partition by dc) from table which is giving completely wrong answer. I also need order by cloub_count partion by items. So the final result should look like this:

item     dc   club_count
    1    200   3
    1    100   2
    1    300   1
    2    200   3
    2    100   1

Answer 1

You only need a simple aggregation here, without window functions:

SELECT item, dc, COUNT(*) AS club_count
FROM yourTable
GROUP BY item, dc
ORDER BY item, dc;

If you really wanted to do this using window functions, you could try:

WITH cte AS (
    SELECT item, dc, COUNT(*) OVER (PARTITION BY item, dc) club_count
    FROM yourTable
)

SELECT DISTINCT item, dc, club_count
FROM cte
ORDER BY item, dc;