How to add a row number within a group in my query

Question

I've tried this query: ``` SELECT X,Y,Z,COUNT(*) FROM TABLE1 GROUP BY X,Y,Z ``` and my result is: but I need the following result:

Accepted Answer

This should do the trick:

```
SELECT X,Y,Z,ROW_NUMBER() OVER (PARTITION BY X,Y,Z ORDER BY X,Y,Z)
FROM TABLE1 
```

The ROW_NUMBER() will tick up for every value in the group X,Y,Z, and reset at a next group. The ORDER BY clause is used to define in what order it should tick up, and can be changed to however you wish. This is one of the analytical functions Oracle provides, and can be very useful.

How to add a row number within a group in my query

Solution

Problem