CODEWITHSUNDEEP
mysqlsql
Muhammad Faizan Khan
Muhammad Faizan Khan

Reputation: 10561

Do i use count(*) to count record or i add count column into my table directly

My team lead insisting me to add days entry count column within table and update it regularly. something like this

  1. Get previous record
  2. take count column value
  3. Add .5 into that value
  4. And update the count record in current record

like this

While I think that this is not the right way. I can count [using Count(*)] the record to display days which is easy why i bother to add it, use update command to know previous entry etc. The reason he told that we can get count directly without query bunch of records which is performance wise is fast. How you do this? what is correct way?

Upvotes: 0

Views: 77

Answers (3)

DeeJay14
DeeJay14

Reputation: 109

UPDATE YourTable 
   SET COUNT_COLUMN = (SELECT MAX(COUNT_COLUMN) + 0.5 
                         FROM YourTable
                       )
 WHERE "Your condition for the current record";

For better performance add index on to COUNT_COLUMN column of YourTable.

Upvotes: 1

Santosh Paswan
Santosh Paswan

Reputation: 84

Hi Fizan,

You can achieve this using function. You can create a function to get resultant value and update it in you column. Like this - 

CREATE function Get_Value_ToBeUpdated
RETURN DECIMAL(10,2)
AS
BEGIN
    DECLARE result decimal (10,2);
    DECLARE previousValue decimal (10,2);
    DECLARE totalCount int;

    SELECT previousValue = SELECT MAX(columnName) FROM YourTable order by primaryColumn desc
    SELECT totalCount    = SELECT COUNT(1) FROM YourTable

    SET result = ISNULL(previousValue,0) + ISNULL(totalCount,0)

    RETURN result;
END

UPDATE YourTable SET COLUMNNAME  = DBO.Get_Value_ToBeUpdated() WHERE Your condition

Thanks :)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270061

If I understand correctly, you just want row_number() divided by 2:

select t.*,
       (row_number() over (order by ??) ) / 2.0
from t;

The ?? is for whatever column specifies the ordering of the table that you want.

Upvotes: 1

Related Questions