How do I add a row total?

[caliguy1979]

Hi,

I don't know how exactly how to do this.

So for example I have the following set of data:

Table: tbl_country_costs
Country Type Cost
North America Sales 2000000
North America Expenses 1250000
North America Taxes 250000
North America Profit 500000
Europe Sales 2500000
Europe Expenses 1250000
Europe Taxes 500000
Europe Profit 750000
South America Sales 500000
South America Expenses 250000
Asia Sales 800000
Asia Expenses 350000
Asia T axes 100000

SELECT COUNTRY,
SUM(CASE WHEN Type='SALES' THEN COST END) AS SALES
SUM(CASE WHEN Type='Expenses' THEN COST END) AS Expenses
SUM(CASE WHEN Type='Taxes' THEN COST END) AS Taxes
SUM(CASE WHEN Type='Profit' THEN COST END) AS Profit
FROM tbl_country_costs
GROUP BY COUNTRY
WITH ROLLUP COUNTRY

That gives me:
Country Sales Expenses Taxes Profit
North America 2000000 1250000 250000 500000
Europe 2500000 1250000 500000 750000
South America 500000 250000 null null
Asia 800000 350000 100000 null
5800000 3100000 850000 1250000 <-- this is the totals for each column

Question is how do I make it so that i'm doing a horizontal sum also? For example:
Country Sales Expenses Taxes Profit Total
North America 2000000 1250000 250000 500000 4000000 <-- total for the row
Europe 2500000 1250000 500000 750000 5000000 <-- total for the row
South America 500000 250000 null null 750000 <-- total for the row
Asia 800000 350000 100000 null 1250000 <-- total for the row
5800000 3100000 850000 1250000

Also the argregate function does not have to be sum. It could min, max, or avg.

Thank you,
-Tesh

[padtes]

A quick idea (not tested)

SELECT COUNTRY,
SUM(CASE WHEN Type='SALES' THEN COST END) AS SALES,
SUM(CASE WHEN Type='Expenses' THEN COST END) AS Expenses,
SUM(CASE WHEN Type='Taxes' THEN COST END) AS Taxes,
SUM(CASE WHEN Type='Profit' THEN COST END) AS Profit,
sum(when type in ('SALES','Expenses','Taxes','Profit') then cost end) as RowTot
FROM tbl_country_costs
GROUP BY COUNTRY
WITH ROLLUP

[caliguy1979]

padtes...thanks for the post. I tried your suggestion and it did not work.

My friend did suggest:

"Just add SUM(COST) as Total to your query. This will be the total as you are grouping by country already." That seems like it should work. Trying it now...looks good.

-tesh