CODEWITHSUNDEEP
mysqlsqlselectsum
Sai Sai
Sai Sai

Reputation: 115

How to perform SUM of rows in MySQL

I have a table called temp_reading. It has the following columns (consumption is an index key):

id consumption total
1  100
1  200
1  300
1  400
2  50
2  100
3  200
4  250

Now I want to display total as

id   consumption  total
1    100          100
1    200          300
1    300          600
1    300          900
2    50           50
2    100          150
3    200          200
4    250          250

Is it possible to display like the above?

I tried the following query:

SELECT id,consumption,sum(consumption) as total 
FROM temp_reading 
GROUP BY consumption;

Please help me solve this.

Upvotes: 3

Views: 2085

Answers (3)

Daniel
Daniel

Reputation: 9

     select id,sum(consumption) as total 
     from temp_reading 
     group by id;

I suggest you do not have the same ID (1,1,1.2,2...)

Upvotes: 1

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this: 

SELECT id, consumption, IF(@s=@s:=id, @s2:=@s2+consumption, @s2:=consumption) AS total
FROM temp_reading, (SELECT @s:=0, @s2:=0);

Upvotes: 0

KaeL
KaeL

Reputation: 3659

I recommend that you add a Primary Key on your temp_reading table. (Read more about Primary Keys) This key will be unique per row. Then you can try this query:

SELECT TR.id
  , TR.consumption
  , TR.consumption + IFNULL(SUM(TR2.consumption), 0) AS Total
  FROM temp_reading TR
  LEFT JOIN temp_reading TR2 ON TR.id = TR2.id AND TR.pk > TR2.pk
  GROUP BY TR.pk;

I've tried it in SQL Fiddle.

Upvotes: 4

Related Questions