Total with select query

Question

Hi,

Consider the following data:

Insurance_Comp | 1To30DaysAgeing | 31To60DaysAgeing | 61To90DaysAgeing | TotalVehicles

=============================================================================

ABC | 30 | 5 | 20 | 55

XYZ | 10 | 35 | 5 | 50

I am calculating the number of vehicles aged for particular group after a stock# is assigned to that vehicle. The number of vehicles for a group (ex. 1 to 30 Days Ageing) is calculated using a complex query. I have written SP to get this result. What I want is to calculate the total of vehicles while executing the same select query. For simplification I have created functions in SQL to get number of vehicles for each group.

Right now I m using the query like this ...

Select Ins_Comp, dbo.fn_1To30Ageing(...), dbo.fn_31To60Ageing(...), dbo.fn_61To90Ageing(...) from Table Where ....

I am calculating the total using RowDataBound event of GridView in ASP.NET with C#. Is there any way to calculate the total within query itself? By the way I dont want total as dbo.fn_1To30Ageing(...)+ dbo.fn_31To60Ageing(...) + dbo.fn_61To90Ageing, because that requires double processing time.

Thanks for sharing your valuable time...

Answer 1

I'm not familir with ASP.NET, but if it is only SQL aspect, you can certainly use sub-query:

SELECT A,B,C,SUM(A+B+C) FROM ( Select Ins_Comp, dbo.fn_1To30Ageing(...) AS A, dbo.fn_31To60Ageing(...) AS B, dbo.fn_61To90Ageing(...) AS C from Table Where .... ) TEMP