Sum totals for columns

Question

How can I get a sum for all columns? Say I have a table like the one below. My query gets the total costs and net return. How can I also calculate the "total" sums without writing a second query.

Date        rate      supplyfee     demandfee     chargedfee   othersTotalcosts  netReturn 
2010-01-10  1,339.00  2,150.00     10,000.00       120,000.00        1,085.78     1,064.22
2011-01-01  3,339.00  1,150.00     1,100.00        150,000.00        1,144.13      5.87
*Total*               3,300.00     2,100.00

Total costs is computed by sum(chargedfee + others )/rate + demandfee and net return is computed by supplyfee - totalcosts.

The query below computes total costs:

 SELECT date
        , rate
        , supply_fee
        , demand_fee
        , charged_fee
        , (charged + others/rate) + demandfee AS totalcosts
        , supplyfee-((charged + others/rate) + demandfee  AS net returns

 FROM  financies
 WHERE date BETWEEN '2010-01-10' AND '2011-01-01'

Answer 1

This is going to look complicated, but bear with me. It needs some clarification on what is meant by others/rate however the principle is sound. If you have a primary key on financies that you can use then a more elegant (GROUP BY ... ROLLUP) solution may be viable however I've not sufficient experience with that to offer reliable advice. Here goes how I would address the issue.

Long-winded option

(
    SELECT
        financesTallied.date,
        financesTallied.rate,
        financesTallied.supply_fee,
        financesTallied.demand_fee,
        financesTallied.charged_fee,
        financesTallied.total_costs,
        financesTallied.net_return

    FROM (

        SELECT
            financeWithNetReturn.*,
            @supplyFee := @supplyFee + financeWithNetReturn.supply_fee,
            @demandFee := @demandFee + financeWithNetReturn.demand_fee,
            @charedFee := @charedFee + financeWithNetReturn.charged_fee
        FROM 
        ( // Calculate net return based off total costs
            SELECT 
                financeData.*,
                financeData.supply_fee - financeData.total_costs AS net_return
            FROM 
            ( // Select the data
                SELECT
                    date, 
                    rate, 
                    supply_fee, 
                    demand_fee, 
                    charged_fee,
                    (supply_fee+demand_fee+charged_fee)/rate AS total_costs // need clarification on others/rate
                FROM financies
                WHERE date BETWEEN '2010-01-10' AND '2011-01-01'
                ORDER BY date ASC
            ) AS financeData
        ) AS financeWithNetReturn,
        (
            SELECT
                @supplyFee := 0
                @demandFee := 0
                @charedFee := 0
        ) AS variableInit
    ) AS financesTallied
) UNION (
    SELECT
        '*Total*',
        NULL,
        @supplyFee,
        @demandFee,
        @chargedFee,
        NULL,
        NULL
)

Working from the innermost query to the outermost. This query selects the basic fees and calculates the total_costs for this row. This total_costs formula will need adjustment as I'm not 100% clear on what you were looking for there. Will refer to this as [SQ1]

            SELECT
                date, 
                rate, 
                supply_fee, 
                demand_fee, 
                charged_fee,
                (supply_fee+demand_fee+charged_fee)/rate AS total_costs // need clarification on others/rate
            FROM financies
            WHERE date BETWEEN '2010-01-10' AND '2011-01-01'
            ORDER BY date ASC

Next level up I'm just reusing the calculated total_costs column with the supply_fee column to add in a net_return column. This concludes the basic data you need per-row, will refer to this as [SQL2]

        SELECT 
            financeData.*,
            financeData.supply_fee - financeData.total_costs AS net_return
        FROM 
        ([SQ1]) AS financeData

At this level it's time to start tallying up the values, so need to initialise the variables required with 0 values ([SQL3])

        SELECT
            @supplyFee := 0
            @demandFee := 0
            @charedFee := 0 

Next level up, I'm using the calculated rows to calculate the totals ([SQL4])

    SELECT
        financeWithNetReturn.*,
        @supplyFee := @supplyFee + financeWithNetReturn.supply_fee,
        @demandFee := @demandFee + financeWithNetReturn.demand_fee,
        @charedFee := @charedFee + financeWithNetReturn.charged_fee
    FROM 
    ([SQL2]) AS financeWithNetReturn,
    ([SQL3]) AS variableInit

Now finally at the top level, just need to output the desired columns without the calculated columns ([SQL5])

SELECT
    financesTallied.date,
    financesTallied.rate,
    financesTallied.supply_fee,
    financesTallied.demand_fee,
    financesTallied.charged_fee,
    financesTallied.total_costs,
    financesTallied.net_return

FROM ([SQL4]) AS financesTallied

And then output it UNIONED with a totals row

([SQL5]) UNION (
    SELECT
        '*Total*',
        NULL,
        @supplyFee,
        @demandFee,
        @chargedFee,
        NULL,
        NULL
)

Answer 2

You can use WITH ROLLUP with a GROUP BY clause to summurize your columns, something like:

select IFNULL(date, "Total") date, IFNULL(rate, "") rate, 
       supply_fee, demand_fee, charged_fee,
       (charged + others/rate) + demandfee as totalcosts,
      supplyfee-((charged + others/rate) + demandfee  as net returns
from financies
Group by date, rate WITH ROLLUP
Having date between '2010-01-10' AND '2011-01-01'

Answer 3

wrap it and add another column similar to this:

select date, rate, supply_fee, demand_fee, charged_fee, totalcosts, net_returns, 
 (totalcosts+net_returns+supply_fee) as grandtotal
from(
    select date, rate, supply_fee, demand_fee, charged_fee,
           (charged + others/rate) + demandfee as totalcosts,
           supplyfee-((charged + others/rate) + demandfee as net_returns
    from finances
    where date between '2010-01-10' AND '2011-01-01')