jjj2k
04-05-2011, 11:43 PM
Hi all,
The following script creates the following table:
select coalesce(cast(aa1.carriage_type as varchar(20)), 'Total Carriage') as 'Carriage_Type',
round ( sum(aa1.new_charge), 0 ) as 'New Charge Ex GST'
from
( --AA1
select carriage_type, bss.New_Charges_Ex_Gst as new_charge from dbo.tbl_Accounts
inner join dbo.tbl_BillSummary bss on dbo.tbl_Accounts.ID = bss.Account_ID
) AA1
group by AA1.Carriage_Type with Rollup
Carriage_Type New Charge Ex GST
Fixed Data 11199965.00
Fixed Voice 1046443.00
Mobile Voice 134342.00
Total Carriage 12380750.00
I want to create a new row that says "Total Voice" which is sum of Fixed Voice and Mobile Voice
and "Total Data" which is sum of Fixed Data and other data types I have.
Is it easy to do in SQL 2008? I know I am not supposed to do this is SQL but i don't want to repeat it for 80 databases in Excel either, rather do it in SQL and use the query to import the table as a whole.
The following script creates the following table:
select coalesce(cast(aa1.carriage_type as varchar(20)), 'Total Carriage') as 'Carriage_Type',
round ( sum(aa1.new_charge), 0 ) as 'New Charge Ex GST'
from
( --AA1
select carriage_type, bss.New_Charges_Ex_Gst as new_charge from dbo.tbl_Accounts
inner join dbo.tbl_BillSummary bss on dbo.tbl_Accounts.ID = bss.Account_ID
) AA1
group by AA1.Carriage_Type with Rollup
Carriage_Type New Charge Ex GST
Fixed Data 11199965.00
Fixed Voice 1046443.00
Mobile Voice 134342.00
Total Carriage 12380750.00
I want to create a new row that says "Total Voice" which is sum of Fixed Voice and Mobile Voice
and "Total Data" which is sum of Fixed Data and other data types I have.
Is it easy to do in SQL 2008? I know I am not supposed to do this is SQL but i don't want to repeat it for 80 databases in Excel either, rather do it in SQL and use the query to import the table as a whole.