combine two select statement - 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

SQL Server 2012 Forums

Transact-SQL (2012)

combine two select statement

Author

Topic

Mikehjun
Starting Member

24 Posts

Posted - 2013-04-10 : 18:31:09

I have two select statement and want to combine into one.
The first one return like this;

1st 2nd 3rd 4th
34 12 99 85

The second one return like this;
Total
230

I want the final looks like this;
1st 2nd 3rd 4th total
34 12 99 85 230

My sql statement like this;

DECLARE @fisyear1 INT, @fisyear2 INT, @fisyear3 INT
SET @fisyear1 = 2012
SET @fisyear2 = @fisyear1*10000 + 701
SET @fisyear3 = @fisyear1*10000 + 10930

SELECT *
FROM
(
SELECT
SUM(S.[Shape_STLength__]) AS "TOTAL"
, CASE
WHEN MONTH(I.[ACTFINISHDATE]) BETWEEN 7 AND 9 THEN '1st'
WHEN MONTH(I.[ACTFINISHDATE]) BETWEEN 10 AND 12 THEN '2nd'
WHEN MONTH(I.[ACTFINISHDATE]) BETWEEN 1 AND 3 THEN '3rd'
WHEN MONTH(I.[ACTFINISHDATE]) BETWEEN 4 AND 6 THEN '4th'
END AS QUARTER
FROM [CLGT].[azteca].[INSPECTION] I
INNER JOIN [LGDM].[GIS].[SSGRAVITYMAIN] S
ON I.ENTITYUID = S.FACILITYID
WHERE (I.INSPTEMPLATENAME = 'Line Cleaned') AND (I.STATUS = 'CLOSED') and I.[ACTFINISHDATE] >= CONVERT(datetime, convert(char(8),@fisyear2))
GROUP BY MONTH(I.[ACTFINISHDATE]), DATEPART("YY", I.[ACTFINISHDATE])
) P
PIVOT (
SUM(TOTAL) FOR QUARTER IN([1st],[2nd],[3rd],[4th])
) AS quarterday

SELECT SUM(S.[Shape_STLength__]) AS "TOTAL"
FROM [CLGT].[azteca].[INSPECTION] I
INNER JOIN [LGDM].[GIS].[SSGRAVITYMAIN] S
ON I.ENTITYUID = S.FACILITYID
WHERE (I.INSPTEMPLATENAME = 'Line Cleaned') AND (I.STATUS = 'CLOSED')
and I.[ACTFINISHDATE] >= CONVERT(datetime, convert(char(8),@fisyear2))
and I.[ACTFINISHDATE] <= CONVERT(datetime, convert(char(8),@fisyear3))

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-11 : 01:38:26

--May be this?


;WITH PivotCte AS
(
 -- Your Pivot Query
)
SELECT [1st],[2nd],[3rd],[4th], ISNULL([1st],0)+ISNULL([2nd],0)+ISNULL([3rd],0)+ISNULL([4th],0) Total
FROM PivotCte

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-11 : 02:30:03

see

http://visakhm.blogspot.in/2012/04/display-total-rows-with-pivotting-in-t.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources