CODEWITHSUNDEEP
mysqlsqlruntime
Methew
Methew

Reputation: 365

Sum of result generated at run time in mysql query

I have a table in MySQL which has a lot of data. I want to show sum of the generated column...

I am subtracting two columns and I want to show SUM of the result. Here is what I have

 CREATE TABLE amounts(
id MEDIUMINT NOT NULL AUTO_INCREMENT,
bank_amount INT,
amount INT,
PRIMARY KEY (id)
) ENGINE=MyISAM;
INSERT INTO `amounts` (`id`, `bank_amount`, `amount`) VALUES (NULL, '1000', '100'),
(NULL, '2000', '200'),(NULL, '3000', '300'),(NULL, '1200', '500'),(NULL, '2000', '600'),
(NULL, '1202',   '800'),(NULL, '220', '50');

I have this query

SELECT bank_amount,amount,(bank_amount-amount) as bal from amounts where ......

Above query will give me this result

bank_amount amount bal

1000 100 900

2000 200 1800

3000 300 2700

and so on

Now I want to show SUM of bal also like 900+1800+2700

Upvotes: 0

Views: 305

Answers (2)

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

Are you looking for

SELECT
    A.id,
    A.bank_amount,
    A.amount,
    (A.bank_amount - A.amount) as bal,
    T.Total_bal
FROM amounts A
    JOIN 
      (
          SELECT Id, SUM(bank_amount-amount) Total_bal
          FROM amounts
          GROUP BY Id
      ) T
      ON A.Id = T.Id

?

Upvotes: 0

Strawberry
Strawberry

Reputation: 33945

Is this what you mean?

SELECT x.*
      , x.bank_amount-x.amount bal
      , SUM(y.bank_amount-y.amount) running
   FROM amounts x
   JOIN amounts y
     ON y.id <= x.id
  GROUP
     BY id;
 +----+-------------+--------+------+---------+
 | id | bank_amount | amount | bal  | running |
 +----+-------------+--------+------+---------+
 |  1 |        1000 |    100 |  900 |     900 |
 |  2 |        2000 |    200 | 1800 |    2700 |
 |  3 |        3000 |    300 | 2700 |    5400 |
 |  4 |        1200 |    500 |  700 |    6100 |
 |  5 |        2000 |    600 | 1400 |    7500 |
 |  6 |        1202 |    800 |  402 |    7902 |
 |  7 |         220 |     50 |  170 |    8072 |
 +----+-------------+--------+------+---------+

Upvotes: 2

Related Questions