How do i add a total column for each row? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Analysis Services (2000)

How do i add a total column for each row?

Author

Topic

caliguy1979
Starting Member

2 Posts

Posted - 2009-09-14 : 20:18:03

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 Taxes 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 aggregate function does not have to be sum. It could min, max, or avg.

Thank you,
-Tesh

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-09-15 : 04:45:18

Just add SUM(COST) as Total to your query. This will be the total as you are grouping by country already.

caliguy1979
Starting Member

2 Posts

Posted - 2009-09-15 : 12:31:03

Thanks Rick. That works. Glad it was that simple to do.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources