I've got a cursor that I am generating. If I don't group view it looks something like:
Num RNum Subtotal Location
1 .NULL. .NULL. 2
3 100 50.00 2
25 144 185.00 2
3 .NULL. .NULL. 3
15 34 200.00 3
Now I'd like to group these by location. I also need to count how many records there are for each location, and the total "subtotal" amount. No problem, in my view I do a COUNT(Table1.Num) AS Num to get the number of records, a SUM(Table1.Subtotal) AS Subtotal for the total subtotal.
Here's where I'm having problems. I'd like to count how many records have a value in the Rnum column (i.e. are not .NULL.). For this example, the total # should be 3. I've tried using:
COUNT(IIF(LEN(Table1.Rnum) > 0,1,0)) AS RnumTotal
But it keeps giving me the same number as the total number of records. What am I missing?