Menu
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:
756
First, you need to write a CTE in which you assign a number to each row within each group. To do that, you can use the ROW_NUMBER() function. In OVER() , you specify the groups into which the rows should be divided ( PARTITION BY ) and the order in which the numbers should be assigned to the rows ( ORDER BY ).
To add a row number column in front of each row, add a column with the ROW_NUMBER function, in this case named Row# . You must move the ORDER BY clause up to the OVER clause. SELECT ROW_NUMBER() OVER(ORDER BY name ASC) AS Row#, name, recovery_model_desc FROM sys.
The Row_Number function is used to provide consecutive numbering of the rows in the result by the order selected in the OVER clause for each partition specified in the OVER clause. It will assign the value 1 for the first row and increase the number of the subsequent rows.
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.
120
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
