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'

Tried to reformat your post, but as net returns and '2010-01-10 AND' '2011-01-01' don't look right, and you open two parenthesis while only closing one... — Peter Lang
– Peter Lang, Commented Feb 22, 2012 at 12:40
Your formulae need clarification, they don't really make much sense verbatim — Simon at The Access Group
– Simon at The Access Group, Commented Feb 22, 2012 at 13:07
The total costs are computed by adding chargedfee with others(othersfees) then divided by the rate then adding the demandfee .the net returns are computed by subtracting supply fee from total costs hope this clarifies for the computations — smilezjim
– smilezjim, Commented Feb 22, 2012 at 14:30

Simon at The Access Group · Accepted Answer · 2012-02-23 10:22:01Z

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 )

No problem, I quite enjoy the interesting ones. Cleaned up my answer a bit - might be an idea to clean up these comments a bit too (will delete this one shortly)

Randy · Accepted Answer · 2012-02-22 12:42:15Z

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')

Mahmoud Gamal · Accepted Answer · 2012-02-22 13:08:23Z

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'

The query above runs successfully but zero results set is returned could the issue be with the query its self?
@smilezjim, check your data, Is there any data matching the criteria in the Having clasue??

Collectives™ on Stack Overflow

Sum totals for columns

3 Answers 3

1 Comment

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

2 Comments

Related