getting the total consumption

Question

if i have the following tables:

create table rar (
rar_id int(11) not null auto_increment primary key, 
rar_name varchar (20)); 

create table data_link(
id int(11) not null auto_increment primary key,
rar_id int(11) not null,
foreign key(rar_id) references rar(rar_id));

create table consumption (
id int(11) not null,
foreign key(id) references data_link(id),
consumption int(11) not null,
total_consumption int(11) not null, 
date_time datetime not null);

i want the total consumption to be all the consumption field values added up. Is there a way to accomplish this through triggers? or do i need to each time read all the values + the latest value, sum them up and then update the table? is there a better way to do this?

--------------------------------------------------
id | consumption | total_consumption | date_time  |
==================================================|
1  |      5      |         5         | 09/09/2013 |
2  |      5      |         10        | 10/09/2013 |
3  |      7      |         17        | 11/09/2013 |
4  |      3      |         20        | 11/09/2013 |
--------------------------------------------------

just wondering if there is a cleaner faster way of getting the total each time a new entry is added?

Or perhaps this is bad design? Would it better to have something like: SELECT SUM(consumption) FROM consumption WHERE date BETWEEN '2013-09-09' AND '2013-09-11' in order to get this type of information... would doing this be the best option? The only problem i see with this is that the same command would be re-run multiple times - where each time the data would not be stored as it would be retrieved by request....it could be inefficient when you are re-generating the same report several times over for viewing purposes.... rather if the total is already calculated all you have to do is read the data, rather than computing it again and again... thoughts?

any help would be appreciated...

I'm not sure if I'd leave total consumption as it's own column. Store the ID, consumption, and date...then use a sum statement to pull out the total_consumption whenever you need. — Twelfth, Sep 09 '13 at 21:58
maybe this can help you: http://stackoverflow.com/questions/17664436/cumulative-sum-over-a-set-of-rows-in-mysql You can spare yourself from storing the "cummulative sum", and calculate it *on the fly*. — Barranka, Sep 09 '13 at 22:00
or use `view` like `SELECT SUM(consumption) from consumption;` — jaczes, Sep 09 '13 at 22:01

jaczes · Answer 1 · 2013-09-10T06:52:27.800

if You MUST have trigger - it shoud be like that:

DELIMITER $$

CREATE
TRIGGER `chg_consumption` BEFORE INSERT ON `consumption` 
FOR EACH ROW BEGIN

SET NEW.total_consumption=(SELECT 
                            MAX(total_consumption)+new.consumption 
                           FROM consumption); 
END;
$$

DELIMITER ;

p.s. and make total_consumption int(11) not null, nullable or default 0

EDIT: improve from SUM(total_consumption) for MAX(total_consumption) as @calcinai suggestion

score 1 · Accepted Answer · answered Sep 09 '13 at 22:57

If you've got an index on total_consumption it won't noticeably slow the query down to have a nested select of MAX(total_consumption) as part of the insert as the max value will be stored already.

eg.

INSERT INTO `consumption` (consumption, total_consumption) 
VALUES (8, 
    consumption + (SELECT MAX(total_consumption) FROM consumption)
);

I'm not sure how you're using the id column but you can easily add criteria to the nested select to control this.

If you do need to put a WHERE on the nested select, make sure you have an index across the fields you use and then the total_consumption column. For example, if you make it ... WHERE id = x, you'll need an index on (id, total_consumption) for it to work efficiently.

getting the total consumption

2 Answers2