53

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:

enter image description here

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
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user1582928
  • 535
  • 1
  • 5
  • 5

2 Answers2

53

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
rogerdpack
  • 62,887
  • 36
  • 269
  • 388
G.S
  • 777
  • 2
  • 11
  • 21
22
DENSE_RANK() OVER (PARTITION BY date ORDER BY cat)
Nahuel Fouilleul
  • 18,726
  • 2
  • 31
  • 36
  • Thanks, this is a step in the right direction. However, the fact is that my actual data set is not ordered by cat. Do you know how it would work in that example? (Imagine that in my cat column, DEF comes before ABC. But it is true that DEF values are all consecutive in my ordering so you wouldnt see DEF, DEF, ABC, ABC, DEF as values in the cat column) – user1582928 Sep 20 '12 at 19:00
  • 2
    @user1582928 As per http://msdn.microsoft.com/en-us/library/ms173825.aspx " Determines the order in which the DENSE_RANK function is applied to the rows in a partition". And you can always add ORDER BY at the end of SELECT. – prashanth Sep 20 '12 at 19:38
  • @user1582928, if you want you can specify a function to change order, for example: order by decode('DEF',1,'ABC',2,3) – Nahuel Fouilleul Sep 21 '12 at 07:18