CODEWITHSUNDEEP
mysqlphpmyadmin
sadi
sadi

Reputation: 611

how get the sum of columns at the end of table

hi i select this from database now i want the sum of column s.fare and s.commission as total fare and total commission at the end of column below it in new row.i use union but i only want totals at the last row this vehicle fare

 SELECT s.fare, s.commission, o.driver, s._date, v.vehiclenumber, p.productname,
        s.source, s.destination, rs.routename, rd.routename, o.ownername
 FROM route AS rs, route AS rd, shipment AS s, product AS p, vehicle AS v,
      owner AS o
    WHERE s.vehicle = v.vehicleid
    AND s.source = rs.routeid
    AND v.owner = o.ownerid
    AND s.destination = rd.routeid
    AND s.product = p.productid
    AND v.vehiclenumber =  'nk-234'
    ORDER BY _date
    LIMIT 0 , 30

Upvotes: 0

Views: 762

Answers (2)

RichardTheKiwi
RichardTheKiwi

Reputation: 107726

It is quite tricky because you want to SUM only among the LIMIT 0,30

SELECT fare, commission, driver, _date, vehiclenumber, productname,
    source, destination, routename, routename, ownername
FROM
(
    SELECT *
    FROM 
    (
        SELECT 1 as Part,
            s.fare, s.commission, o.driver, s._date, v.vehiclenumber, p.productname,
            s.source, s.destination, rs.routename as rs_routename, rd.routename, o.ownername
        FROM route AS rs, route AS rd, shipment AS s, product AS p, vehicle AS v, owner AS o
        WHERE s.vehicle = v.vehicleid
        AND s.source = rs.routeid
        AND v.owner = o.ownerid
        AND s.destination = rd.routeid
        AND s.product = p.productid
        AND v.vehiclenumber =  'nk-234'
        ORDER BY _date
        LIMIT 0 , 30
    ) Part1
    UNION ALL
    SELECT 2 as Part,
        SUM(fare), SUM(commission), null, null, null, null,
        null, null, null, null, null
    FROM
    (
        SELECT
            s.fare, s.commission, o.driver, s._date, v.vehiclenumber, p.productname,
            s.source, s.destination, rs.routename as rs_routename, rd.routename, o.ownername
        FROM route AS rs, route AS rd, shipment AS s, product AS p, vehicle AS v, owner AS o
        WHERE s.vehicle = v.vehicleid
        AND s.source = rs.routeid
        AND v.owner = o.ownerid
        AND s.destination = rd.routeid
        AND s.product = p.productid
        AND v.vehiclenumber =  'nk-234'
        ORDER BY _date
        LIMIT 0 , 30
    ) Part2
) Joined
ORDER BY Part, _date

Upvotes: 1

Vladimir Pikalov
Vladimir Pikalov

Reputation: 1

Use construction similar to (just add necessary columns instead of Price):

SELECT Price, 0 AS LastRow FROM Orders WHERE Price > 10
UNION
SELECT SUM(Price), 1 AS LastRow FROM Orders WHERE Price > 10
ORDER BY LastRow

Upvotes: 0

Related Questions