Union - SQL Server Forums

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

Union

Author

Topic

Msandlana
Starting Member

33 Posts

Posted - 2008-02-11 : 07:00:58

Hi All
I want to do the following report and I want the 2nd select statement to appear at the bottom to display the totals

SELECT Item,B.Mar, B.Apr, B.May, B.Jun,B.Jul, B.Aug, B.Sep, B.Oct, B.Nov, B.Dec, B.Jan
FROM fncWineSales(2007) AS A
UNION
SELECT 'Total',SUM(B.Mar), SUM(B.Apr), SUM(B.May), SUM(B.Jun),SUM(B.Jul), SUM(B.Aug), SUM(B.Sep), SUM(B.Oct), SUM(B.Nov), SUM(B.Dec), SUM(B.Jan)
FROM dbo.fncWineSales (2007) AS B

thanx in advance

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-11 : 07:03:57

If you use Reports then make use of SUM aggregate function and dont use second select statement

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-11 : 07:05:13

Which is your front end? If its SQL reporting services you dont have to do this using SQL code. your first select itself is enough and you can perform this aggregation in report itself.

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2008-02-11 : 07:07:03

If you are using report services/report server within SQL server 2003 or SQL server 2005, you can do aggregate SUM function, on layout tab, where you define the outlook of report.

very simple.

Msandlana
Starting Member

33 Posts

Posted - 2008-02-11 : 07:13:00

I am not using report this is just a stored procedure will show the items on specific month and display the bellow

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-11 : 07:25:16

Or use WITH ROLLUP or WITH CUBE?

E 12°55'05.25"
N 56°04'39.16"

Msandlana
Starting Member

33 Posts

Posted - 2008-02-11 : 08:09:19

DOESN'T WORK IN SQL SERVER 2005

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-11 : 08:09:31

quote:
Originally posted by Msandlana

I am not using report this is just a stored procedure will show the items on specific month and display the bellow

Where do you want to show data?

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-11 : 08:33:33

quote:
Originally posted by Msandlana

DOESN'T WORK IN SQL SERVER 2005

I think you need to test before saying anything like that

SELECT		MONTH(OrderDate) AS theMonth,
		COUNT(*) AS theOrders
FROM		AdventureWorks.Sales.SalesOrderHeader
GROUP BY	MONTH(OrderDate)
WITH		ROLLUP
ORDER BY	COALESCE(MONTH(OrderDate), 13)

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-11 : 08:40:48

quote:
Originally posted by Msandlana

I am not using report this is just a stored procedure will show the items on specific month and display the bellow

If you're so particular to do it on SQL, use either this or CUBE or ROLLUP as suggested by Peso

SELECT t.Type,t.Mar, t.Apr, t.May, t.Jun,t.Jul, t.Aug, t.Sep, t.Oct, t.Nov, t.Dec, t.Jan
FROM
(
SELECT Item AS Type,B.Mar, B.Apr, B.May, B.Jun,B.Jul, B.Aug, B.Sep, B.Oct, B.Nov, B.Dec, B.Jan,0 AS SortOrder 
FROM fncWineSales(2007) AS A
UNION
SELECT 'Total' AS Type,SUM(B.Mar) AS Mar, SUM(B.Apr) AS Apr, SUM(B.May) AS May, SUM(B.Jun) AS Jun,SUM(B.Jul) AS Jul, SUM(B.Aug) AS Aug, SUM(B.Sep) AS Sep, SUM(B.Oct) AS Oct, SUM(B.Nov) AS Nov, SUM(B.Dec) AS Dec, SUM(B.Jan) AS Jan,1 AS SortOrder
FROM dbo.fncWineSales (2007) AS B
)t
ORDER BY t.SortOrder

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-11 : 08:45:42

Another example with more columns

SELECT		COALESCE(p.OnlineOrder, 'Total') AS OrderType,
		SUM(p.[1]) AS [Jan],
		SUM(p.[2]) AS [Feb],
		SUM(p.[3]) AS [Mar],
		SUM(p.[4]) AS [Apr],
		SUM(p.[5]) AS [May],
		SUM(p.[6]) AS [Jun],
		SUM(p.[7]) AS [Jul],
		SUM(p.) AS [Aug],
		SUM(p.[9]) AS [Sep],
		SUM(p.[10]) AS [Oct],
		SUM(p.[11]) AS [Nov],
		SUM(p.[12]) AS [Dec]
FROM		(
			SELECT	CASE OnlineOrderflag
					WHEN 1 THEN 'Online'
					ELSE 'Offline'
				END AS OnlineOrder,
				MONTH(OrderDate) AS theMonth
			FROM	AdventureWorks.Sales.SalesOrderHeader
		) AS o
PIVOT		(
			COUNT(theMonth)
			FOR	theMonth IN ([1], [2], [3], [4], [5], [6], [7], , [9], [10], [11], [12])
		) AS p
GROUP BY	p.OnlineOrder
WITH		ROLLUP
ORDER BY	CASE
			WHEN p.OnlineOrder IS NULL THEN 1
			ELSE 0
		END,
		p.OnlineOrder DESC

E 12°55'05.25"
N 56°04'39.16"

pravin14u
Posting Yak Master

246 Posts

Posted - 2008-02-12 : 04:20:26

I assume you just need to make sure that the TOTAL row comes as the last row of the result. If my assumption is right, then the following should work

select right(Items,len(items)-1) as Item,* from
(SELECT 'a'+Item as Items,B.Mar, B.Apr, B.May, B.Jun,B.Jul, B.Aug, B.Sep, B.Oct, B.Nov, B.Dec, B.Jan
FROM fncWineSales(2007)
UNION
SELECT 'zTotal' as Items,SUM(B.Mar), SUM(B.Apr), SUM(B.May), SUM(B.Jun),SUM(B.Jul), SUM(B.Aug), SUM(B.Sep), SUM(B.Oct), SUM(B.Nov), SUM(B.Dec), SUM(B.Jan)
FROM dbo.fncWineSales (2007)) A
order by Items

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources