Menu
I have a data set that contains the columns Date, Cat, and QTY. What I want to do is add a unique column that only counts unique Cat values when it does the row count. This is what I want my result set to look like:
By using the SQL query below, I'm able to get row using the row_number() function.
However, I can't get that unique column that I have depicted above. When I add group by to the OVER clause, it does not work. Does anybody have any ideas as how I could get this unique count column to work?
SELECT Date, ROW_NUMBER() OVER (PARTITION BY Date ORDER By Date, Cat) as ROW, Cat, Qty FROM SOURCE 
777
SQL Server Row_Number group by And based upon that partition, Row_Number() function will assign the integer values to each record starting from 1. And on the other side, the Group By statement in SQL Server is used to group rows that have the same values.
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.
ROW_NUMBER numbers all rows sequentially (for example 1, 2, 3, 4, 5). RANK provides the same numeric value for ties (for example 1, 2, 2, 4, 5). ROW_NUMBER is a temporary value calculated when the query is run. To persist numbers in a table, see IDENTITY Property and SEQUENCE.
The row_number() window function can be used without order by in over to arbitrarily assign a unique value to each row.
Here is a solution.
You need not worry about the ordering of Cat. Using following SQL you will be able to get unique values for your Date & Cat combination.
SELECT Date, ROW_NUMBER() OVER (PARTITION BY Date, Cat ORDER By Date, Cat) as ROW, Cat, Qty FROM SOURCE 
DENSE_RANK() OVER (PARTITION BY date ORDER BY cat) 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
