MySQL Total Column

[councils]

Hello,

I could do with some help with a MySQL table.
I've got one column for values. What I need is for each time a new row is added to run though that column, add up the existing values, then insert the result into the Total column in that row. Can anyone help?

Thanks.

 

[misson]

Since column totals are a property of the table and not whatever the rows represent, the total shouldn't be stored in rows of the table. Additionally, this redundancy will potentially cause inconsistencies, and it violates third normal form.

Instead, use SUM or WITH ROLLUP in your queries to get the total. SUM is an aggregate function, which means to use it rows must be combined into a single row. If you want separate rows and a total, use WITH ROLLUP or a JOIN.

-- Note: including a non-aggregate, non-grouped column (`name` in this 
-- example) as a selected column is non-standard SQL, but supported by MySQL. 
-- It only works properly because the statement groups by a table key (here, `id`).
SELECT id, name, SUM(amount) 
  FROM table
  GROUP BY id WITH ROLLUP;

-- or

SELECT SUM(t1.amount) 
  FROM table AS t1 
    JOIN table AS t2;

If the above isn't performant enough, and the number of insertions, updates and deletions is likely to be low compared to the number of selections, you can create another table to hold statistics about the first table and use triggers to update the statistics table.

CREATE TABLE table_statistics (
    `name` VARCHAR(32),
    `column` VARCHAR(32),
    `stat` VARCHAR(16),
    `value` FLOAT -- if you also need to store statistics for non-numeric column types, use VARCHAR and store everything as strings
);

INSERT INTO table_statistics 
(`name`, `column`, `stat`, `value`)
  SELECT 'table', 'amount', 'SUM', SUM(amount) FROM `table`;


CREATE TRIGGER add_table_amount
AFTER INSERT ON `table`
FOR EACH ROW
UPDATE table_statistics 
    SET value=value+NEW.amount 
    WHERE name='table' AND `column`='amount' AND `stat`='SUM';

CREATE TRIGGER update_table_amount
BEFORE UPDATE ON `table`
FOR EACH ROW
UPDATE table_statistics 
    SET value=value-OLD.amount +NEW.amount 
    WHERE name='table' AND `column`='amount' AND `stat`='SUM';

CREATE TRIGGER delete_table_amount
AFTER DELETE ON `table`
FOR EACH ROW
UPDATE table_statistics 
    SET value=value-OLD.amount 
    WHERE name='table' AND `column`='amount' AND `stat`='SUM';

If you had posted sample code (a table creation statement, in this case), I could have shown you queries more relevant to your project. You should also describe the what you're trying to achieve with the column total. See the links in my sig for more on asking for help.

 

[descalzo]

Since column totals are a property of the table and not whatever the rows represent, the total shouldn't be stored in rows of the table.

And if the total is just meant to represent "total of values after this item was added"?

The values in the column could change later, making that information impossible to reconstruct.

 

[misson]

And if the total is just meant to represent "total of values after this item was added"?

In any case, it's not clear what councils wants, so we can only guess.

The values in the column could change later, making that information impossible to reconstruct.

If councils wants a running total, changing earlier values is not an insignificant consistency problem. Note that "earlier" isn't well defined according to councils' post: would it refer to insertion order (as you posit), ordering by one of the table fields or by some property of whatever is being modeled which isn't represented in the table? Using views is the stablest approach for running totals; triggers would be problematic.