locked
Inserting counts into a table RRS feed

  • Question

  • Hi:

    I would like to count repeating field values in a table, and to insert the counts into the same table. I have managed by having a temp_table, into which I insert the values:

    insert into temptable values (select count(*) as Count, fieldname from table group by fieldname)

    I then do a join on table.fieldname=temptable.fieldname, and update table.count with temptable.count.

    Is very cumbersome, and does not update counts when table changes.

    Is there a way to put in a calculated member, or to put the vaues of auto stats into the count field?

    TIA

    Kar

    Tuesday, February 20, 2007 11:53 AM

Answers

  • hi karfast,

    you can use UPDATE FROM statement to rid yourself of the temp table

    UPDATE table
           SET table.column = t.columnValue
                 ,table.columnCount = t.columnCountValue
    FROM
           table t1
          INNER JOIN
            (SELECT columnValue, count(*) columnCountValue
              FROM table
              GROUP BY columnValue )  t ON t1.columnValue = t.columnValue


    hth
    Tuesday, February 20, 2007 12:39 PM

All replies

  • hi karfast,

    you can use UPDATE FROM statement to rid yourself of the temp table

    UPDATE table
           SET table.column = t.columnValue
                 ,table.columnCount = t.columnCountValue
    FROM
           table t1
          INNER JOIN
            (SELECT columnValue, count(*) columnCountValue
              FROM table
              GROUP BY columnValue )  t ON t1.columnValue = t.columnValue


    hth
    Tuesday, February 20, 2007 12:39 PM
  • Hi bonskijr,

    Thanx for the response. I can use this and have less cleanup to bother about!

    This will work in batch mode at least. Now is there a more realtime way, such that the count will change when a new row is inserted into the table?

    TIA

    Kar

    Tuesday, February 20, 2007 2:40 PM
  • hi karfast,

    If that's the case use an update trigger to update the rowcount then


    hth
    Wednesday, February 21, 2007 4:40 AM