locked
How to count and enumerate with a counter all records in table with specific field containing value RRS feed

  • Question

  • User1395831461 posted

    I have a table containing a couple of dozen fields the records of which will contain some 30 distinct values in one of the columns. I want to read through the table and enumerate each record with an integer counter to be placed in one of that records columns that indicates the count value of that record sharing one of the 30 distinct fields.

    Each time I read a record I want to see the highest counter value for records with that distinct value in the distinct value column, add one to the count and put the new counter value in the "counter" field for that record.

    Could someone kindly show me how to do this? I'm guessing its going to use an sql task in the control flow but if there is a better way would be happy to use that.  I've tried a couple of things but haven't yet got it right.

    Thanks tonnes for any help, Roscoe

    Distinct Column          Counter Value

    a                                   1

    b                                   1

    b                                   2

    a                                   2

    c                                    1

    c                                    2

    c                                    3

    a                                    3

    a                                    4

    c                                    4

    a                                    5

    Monday, October 31, 2016 1:54 PM

Answers

  • User-271186128 posted

    Hi rpfinnimore,

    Based on your requirement, I suggest you could refer to the following code:

    Before inserting new values into the Table, you could query the table and check whether the count of the new value.

    create table DistinctCounter
    (
    DistinctColumn nvarchar(2),
    CounterValueWithinDistinctColumn int
    )
    go
    insert into DistinctCounter
    select 'a',1 union
    select 'b',1 
    go
    
    
    create procedure InsertDistinct 
    	@insertDistinct nvarchar(2)
    AS
    set nocount on;
    declare @counter int 
    --query datatable and get the counter value
    select @counter = count(CounterValueWithinDistinctColumn) + 1 from  DistinctCounter where DistinctColumn = @insertDistinct;
    
    --insert new values
    insert into DistinctCounter (DistinctColumn, CounterValueWithinDistinctColumn) values (@insertDistinct, @counter);
    --requery select * from DistinctCounter; go


    execute InsertDistinct 'b'

    Best regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 7, 2016 7:55 AM

All replies

  • User-2057865890 posted

    Hi Rpfinnimore,

    Each time I read a record I want to see the highest counter value for records with that distinct value in the distinct value column,

    You could use Count and Group By.

    declare @YourTable table (DistinctColumn nvarchar(10))
    
    INSERT @YourTable VALUES ('a')
    INSERT @YourTable VALUES ('b')
    INSERT @YourTable VALUES ('b')
    INSERT @YourTable VALUES ('a')
    INSERT @YourTable VALUES ('c')
    INSERT @YourTable VALUES ('c')
    INSERT @YourTable VALUES ('c')
    INSERT @YourTable VALUES ('a')
    INSERT @YourTable VALUES ('a')
    INSERT @YourTable VALUES ('c')
    INSERT @YourTable VALUES ('a')
    
    SELECT
        DistinctColumn, COUNT(*) AS CountOf
        FROM @YourTable
        GROUP BY DistinctColumn
    

    Best Regards,

    Chris

    Tuesday, November 1, 2016 7:18 AM
  • User753101303 posted

    Hi,

    My understanding is that you want to have a counter column? Assuming SQL Server see https://msdn.microsoft.com/en-us/library/ms186734.aspx

    Not sure to get how your final result is sorted (on a id not shown in the resultset ???)

    Tuesday, November 1, 2016 2:32 PM
  • User77042963 posted

    Does your table have a column to sort for the order?

    If you do have one, you can look up for solution "gaps and islands in tsql".

    Tuesday, November 1, 2016 3:53 PM
  • User1395831461 posted

    Hi Chris, Thanks for the quick reply.  That code will give me the total count for each distinct value but I'm trying to do something a little different.  I want to enumerate each record with its' count value within the total count for that distinct value.  I would expect to get something looking like below...

    DistinctColumn          CounterValueWithinDistinctColumn

    a                                   1

    b                                   1

    b                                   2

    a                                   2

    c                                    1

    c                                    2

    c                                    3

    a                                    3

    a                                    4

    c                                    4

    a                                    5

    ...thanks tonnes for any help, Cheers, Roscoe

    Tuesday, November 1, 2016 6:52 PM
  • User-271186128 posted

    Hi rpfinnimore,

    Based on your requirement, I suggest you could refer to the following code:

    Before inserting new values into the Table, you could query the table and check whether the count of the new value.

    create table DistinctCounter
    (
    DistinctColumn nvarchar(2),
    CounterValueWithinDistinctColumn int
    )
    go
    insert into DistinctCounter
    select 'a',1 union
    select 'b',1 
    go
    
    
    create procedure InsertDistinct 
    	@insertDistinct nvarchar(2)
    AS
    set nocount on;
    declare @counter int 
    --query datatable and get the counter value
    select @counter = count(CounterValueWithinDistinctColumn) + 1 from  DistinctCounter where DistinctColumn = @insertDistinct;
    
    --insert new values
    insert into DistinctCounter (DistinctColumn, CounterValueWithinDistinctColumn) values (@insertDistinct, @counter);
    --requery select * from DistinctCounter; go


    execute InsertDistinct 'b'

    Best regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 7, 2016 7:55 AM