mysqlcountmariadbequationmysql-variables

MySQL / MariaDB place COUNT(*) in equation


I'm trying to get the number of rows in a table or column and place that value inside an equation, like this:

UPDATE myTable 
SET myCalculatedColumn = COUNT(*) / (@rownum:= 1 + @rownum)
WHERE 0 = (@rownum:=0)

Unfortunately, I get an error 1111 "Invalid use of group function". I've also tried:

SET @c = COUNT(*);
UPDATE myTable 
SET myCalculatedColumn = @c / (@rownum:= 1 + @rownum)
WHERE 0 = (@rownum:=0)

But this produces the same error.

How can I place COUNT(*) (or a programmatically equivalent operation) into an equation?


Solution

  • Join with a subquery that gets the count. You can also initialize the @rownum variable there as well.

    UPDATE myTable AS t
    CROSS JOIN (SELECT COUNT(*) AS count, @rownum := 0 FROM myTable) AS c
    SET myCalculatedColumn = count / (@rownum := 1 + @rownum)
    

    If you don't want to do a cross join, you can use the subquery when setting @c. You just have to tell COUNT(*) what table to count from.

    SET @c = (SELECT COUNT(*) FROM myTable);
    SET @rownum = 0;
    UPDATE myTable 
    SET myCalculatedColumn = @c / (@rownum:= 1 + @rownum);
    

    Note that the order that it assigns to myCalculatedColumn will be arbitrary and unpredictable unless you also have an ORDER BY clause.