How to display Total value as row in query result

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

General SQL Server Forums

New to SQL Server Programming

How to display Total value as row in query result

Author

Topic

Sambasivam
Starting Member

36 Posts

Posted - 2008-11-18 : 11:22:46

I have query return and I want the total (SUM Aggregate) as a Row from the query result. How to do that? I need to total all fields from Beginning_ Balance till Ending_Balance

SELECT DISTINCT
rnk.Rank_Id
,rnk.Rolling_Plan_Display
,[Beginnig_Balance]
,[Hires]
,[Separations_Involuntary]
,[Separations_Voluntary]
,[Transfers_In]
,[Transfers_Out]
,[Promotions_In]
,[Promotions_Out]
,[Leave_Of_Absence_In]
,[Leave_Of_Absence_Out]
,[Ending_Balance]
,[Standard_Hours]
,[Adjustment_Hours]
,[Available_Hours]
,SUM([Beginnig_Balance])OVER() AS BBTotal
FROM [HeadCount_Actual] ha

INNER JOIN Fiscal_Month fm ON ha.Fiscal_Month_Id = fm.Fiscal_Month_Id
INNER JOIN Rank rnk ON ha.Rank_Id = rnk.rank_id

WHERE ha.Business_Unit_Id =
(SELECT Business_Unit_Id from Business_Unit
WHERE Business_Unit_Code = 'US001'
AND Business_Unit_Name = 'Northeast')
AND ha.Location_Id =
(SELECT Location_Id from Location
WHERE Location_Code = '0010L')
AND rnk.Is_Used_For_Planning = 1
AND fm.FY_Month = 'MAR'
--AND fm.FY_YEAR = '2009'

ORDER BY rnk.RANK_Id

Any help is appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-18 : 11:27:05

do you mean this?

SELECT DISTINCT
rnk.Rank_Id
,rnk.Rolling_Plan_Display
,[Beginnig_Balance]
,[Hires]
,[Separations_Involuntary]
,[Separations_Voluntary]
,[Transfers_In]
,[Transfers_Out]
,[Promotions_In]
,[Promotions_Out]
,[Leave_Of_Absence_In]
,[Leave_Of_Absence_Out]
,[Ending_Balance]
,[Standard_Hours]
,[Adjustment_Hours]
,[Available_Hours]
,SUM([Beginnig_Balance])OVER() AS BBTotal,
[Beginnig_Balance]+[Hires]+[Separations_Involuntary]
+[Separations_Voluntary]+[Transfers_In]+[Transfers_Out]+[Promotions_In]
+[Promotions_Out]+[Leave_Of_Absence_In]+[Leave_Of_Absence_Out]+
[Ending_Balance] AS YourNewQunatity
FROM [HeadCount_Actual] ha


INNER JOIN Fiscal_Month fm ON ha.Fiscal_Month_Id = fm.Fiscal_Month_Id
INNER JOIN Rank rnk ON ha.Rank_Id = rnk.rank_id

WHERE ha.Business_Unit_Id =
(SELECT Business_Unit_Id from Business_Unit
WHERE Business_Unit_Code = 'US001'
AND Business_Unit_Name = 'Northeast')
AND ha.Location_Id =
(SELECT Location_Id from Location
WHERE Location_Code = '0010L')
AND rnk.Is_Used_For_Planning = 1
AND fm.FY_Month = 'MAR'
--AND fm.FY_YEAR = '2009'

ORDER BY rnk.RANK_Id

make sure you use COALESCE(field,0) for those which contain NULL values.

Sambasivam
Starting Member

36 Posts

Posted - 2008-11-18 : 11:30:29

NO. Executing this query should result in displaying the SUM() as row at the end of query result.

SELECT DISTINCT
rnk.Rank_Id
,rnk.Rolling_Plan_Display
,[Beginnig_Balance]
,[Hires]
,[Separations_Involuntary]
,[Separations_Voluntary]
,[Transfers_In]
,[Transfers_Out]
,[Promotions_In]
,[Promotions_Out]
,[Leave_Of_Absence_In]
,[Leave_Of_Absence_Out]
,[Ending_Balance]
,[Standard_Hours]
,[Adjustment_Hours]
,[Available_Hours]
,SUM([Beginnig_Balance])OVER() AS BBTotal
,SUM([Hires])OVER() AS HiresTotal
,SUM([Separations_Involuntary]) AS SepInvTotal
FROM [HeadCount_Actual] ha

INNER JOIN Fiscal_Month fm ON ha.Fiscal_Month_Id = fm.Fiscal_Month_Id
INNER JOIN Rank rnk ON ha.Rank_Id = rnk.rank_id

WHERE ha.Business_Unit_Id =
(SELECT Business_Unit_Id from Business_Unit
WHERE Business_Unit_Code = 'US001'
AND Business_Unit_Name = 'Northeast')
AND ha.Location_Id =
(SELECT Location_Id from Location
WHERE Location_Code = '0010L')
AND rnk.Is_Used_For_Planning = 1
AND fm.FY_Month = 'MAR'
--AND fm.FY_YEAR = '2009'

ORDER BY rnk.RANK_Id

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-18 : 11:39:22

SUM() of what? please be clear with posts. At least show what you want with some sample data & output.

Sambasivam
Starting Member

36 Posts

Posted - 2008-11-18 : 11:49:42

Rank_Id Rolling_Name A B C D E F G H I J K L M N SUM(A) SUM(B) SUM©
1 Partner/Principal 43 0 0 0 0 0 0 0 0 0 43 0 0 0 480 0 1
2 Executive Director 4 0 0 0 0 0 0 0 0 0 4 0 0 0 480 0 1
3 Senior Manager 38 0 0 1 0 0 0 0 0 0 37 -40 0 -40 480 0 1
4 Manager 69 0 0 0 0 2 0 0 0 0 67 -80 0 -80 480 0 1
5 Senior 1 57 0 0 3 0 1 0 0 1 0 54 -120 0 -120 480 0 1
6 Senior 2 49 0 1 2 0 0 0 0 0 0 46 -120 0 -120 480 0 1
7 Senior 3 48 0 0 1 0 2 0 0 0 0 45 -120 0 -120 480 0 1
8 Senior 4 1 0 0 0 0 0 0 0 0 0 1 0 0 0 480 0 1
9 Staff/Assistant CSA 1 3 0 0 0 0 0 0 0 0 0 3 0 0 0 480 0 1
11 Staff/Assistant 1 82 0 0 0 0 0 0 0 0 0 82 0 0 0 480 0 1
12 Staff/Assistant 2 76 0 0 1 0 0 0 0 0 0 75 -32 0 -32 480 0 1
13 Intern 6 0 0 6 0 0 0 0 0 0 0 -240 0 -240 480 0 1
26 Non-Employee 4 0 0 0 0 0 0 0 0 0 4 0 0 0 480 0 1

Expected

Rank_Id Rolling_Name A B C D E F G H I J K L M N
1 Partner/Principal 43 0 0 0 0 0 0 0 0 0 43 0 0 0
2 Executive Director 4 0 0 0 0 0 0 0 0 0 4 0 0 0
3 Senior Manager 38 0 0 1 0 0 0 0 0 0 37 -40 0 -40
4 Manager 69 0 0 0 0 2 0 0 0 0 67 -80 0 -80
5 Senior 1 57 0 0 3 0 1 0 0 1 0 54 -120 0 -120
6 Senior 2 49 0 1 2 0 0 0 0 0 0 46 -120 0 -120
7 Senior 3 48 0 0 1 0 2 0 0 0 0 45 -120 0 -120
8 Senior 4 1 0 0 0 0 0 0 0 0 0 1 0 0 0
9 Staff/Assistant CSA 1 3 0 0 0 0 0 0 0 0 0 3 0 0 0
11 Staff/Assistant 1 82 0 0 0 0 0 0 0 0 0 82 0 0 0
12 Staff/Assistant 2 76 0 0 1 0 0 0 0 0 0 75 -32 0 -32
13 Intern 6 0 0 6 0 0 0 0 0 0 0 -240 0 -240
26 Non-Employee 4 0 0 0 0 0 0 0 0 0 4 0 0 0
Total SUM(A) SUM(B) SUM© SUM(D) SUM(E) SUM(F) SUM(G)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-18 : 12:00:06

for this you need to write seperate query seperated by UNION ALL to get totals as a row. If this is for report, its much easier to do at your reporting application. Many application like sql reports have footer sections which provides this format very easily using aggregated functions.

Sambasivam
Starting Member

36 Posts

Posted - 2008-11-18 : 12:01:51

This is for Data Validation. TO execute this query and then compare with frontend.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-18 : 12:04:39

quote:
Originally posted by Sambasivam

This is for Data Validation. TO execute this query and then compare with frontend.

then you need to do using then UNION ALL method which you posted earlier

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=114597

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources