MySQL Query SUM Total Based on Column Value

user_13553946 0
Viewed 23

I have table like this

Order      D.O   Cost   Maintenance    Total
ORD-0005    1    100       50           150
ORD-0005    2    50        120          170
ORD-0006    3    200       100          300
ORD-0006    4    150       50           200

Now I want to have total SUM based on Column 'ORDER' So the result will look like this

Order      D.O   Cost   Maintenance    Total
ORD-0005    1    100       50           320
ORD-0005    2    50        120          320
ORD-0006    3    200       100          500
ORD-0006    4    150       50           500

The total value is sum from all cost+maintenance refer to Order Column

Thank you..really appreciated it

mysql
1 Answers

You could use a sub-query that calculates the SUM and then join this to the output:

SELECT  a.`Order`,
        a.`D.O`,
        a.`Cost`,
        a.`Maintenance`,
        b.`Total`
  FROM  `myTable` a
    INNER JOIN (
               SELECT  `Order`,
                       SUM(`Total`) AS `Total`
                 FROM  `myTable`
                 GROUP BY `Order`
               ) b ON a.`Order` = b.`Order`
  ORDER BY a.`Order`

This will calculate the total value for each order in the sub-query, and then include that total value in the output for each Order.

Output:

Order D.O Cost Maintenance Total
ORD-0005 1 100 50 320
ORD-0005 2 50 120 320
ORD-0006 3 200 100 500
ORD-0006 4 150 50 500

Seen here in this working fiddle.

user_21697620
Related