PIVOT Order and Total

I have a query that I'm haivng a few problems with but hopefully someone can help me out. I've attached a file to this question that shows you the output of the query and hopefully you can see what I'm after.

I need to be able to sort the region column into ID number (it's currently alphabetical) and I would like to be able to add a total of each row in the total column and a column total as well if possible (you can see the fields I'd like to correct highlighted in red within the attached file).

I've also included my code that I'm currently using so hopefully someone can help me here.
SELECT  [regionName], [July] AS [Jul], [August] AS [Aug], [September] AS [Sep], [October] AS [Oct], [November] AS [Nov], [December] AS [Dec], [January] AS [Jan], [February] AS [Feb], [March] AS [Mar], [April] AS [Apr], [May] AS [May], [June] AS [Jun], [Total]
FROM
(
SELECT     TOP (100) PERCENT a.new_regionname AS regionName, a.new_region AS regionID, CASE WHEN DATEPART(yyyy, o.new_fundsreleaseddate) < DATEPART(yyyy, o.new_startupdate) THEN DATENAME(m, o.new_startupdate) 
WHEN DATEPART(m, o.new_fundsreleaseddate) < DATEPART(m, o.new_startupdate) THEN DATENAME(m, o.new_startupdate) ELSE DATENAME(m, o.new_fundsreleaseddate) END AS fundsReleased
 
FROM         FilteredAccount a INNER JOIN
                      FilteredOpportunity o ON a.accountid = o.accountid
WHERE     (o.new_fundsreleaseddate >= '07/01/2008') AND (o.new_fundsreleaseddate <= '06/30/2009') AND 
                      (o.new_producttype <> 6) AND (o.new_applicationstatus = 11) OR
                      (o.new_applicationstatus = 11) AND (o.new_startupdate >= '07/01/2008') AND 
                      (o.new_startupdate <= '06/30/2009') AND (o.new_startupflag = 1)
--GROUP BY a.new_region, a.new_regionname, CASE WHEN DATEPART(m, o.new_fundsreleaseddate) < DATEPART(m, o.new_startupdate) THEN DATENAME(m, o.new_startupdate) ELSE DATENAME(m, o.new_fundsreleaseddate) end
)
s
PIVOT(Count(regionID) for [fundsReleased] IN ([July], [August], [September], [October], [November], [December], [January], [February], [March], [April], [May], [June], [Total])) p
--ORDER BY regionID
 
GO

Open in new window

extractedfile.xls
LVL 2
Steven O'NeillSolutions ArchitectAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pssandhuCommented:
Is it possible for you to post the table structures that you are using in your query and some sample data if possible?
0
deadlyDevCommented:
Try this out...
SELECT  
	[regionName], 
	[July]		AS [Jul], 
	[August]	AS [Aug], 
	[September] AS [Sep], 
	[October]	AS [Oct], 
	[November]	AS [Nov], 
	[December]	AS [Dec], 
	[January]	AS [Jan], 
	[February]	AS [Feb], 
	[March]		AS [Mar], 
	[April]		AS [Apr], 
	[May]		AS [May], 
	[June]		AS [Jun], 
	[July] + [August] + [September] + [October]	+ [November] + [December] + [January]	
		+ [February] + [March] + [April] + [May] + [June] AS [Total]
FROM
(
	SELECT TOP (100) PERCENT 
		a.new_regionname AS regionName, 
		a.new_region AS regionID, 
		CASE 
			WHEN DATEPART(yyyy, o.new_fundsreleaseddate) < DATEPART(yyyy, o.new_startupdate) 
				THEN DATENAME(m, o.new_startupdate) 
			WHEN DATEPART(m, o.new_fundsreleaseddate) < DATEPART(m, o.new_startupdate) 
				THEN DATENAME(m, o.new_startupdate) 
			ELSE DATENAME(m, o.new_fundsreleaseddate) 
		END AS fundsReleased
	FROM FilteredAccount a 
	INNER JOIN FilteredOpportunity o 
		ON a.accountid = o.accountid
	WHERE (o.new_fundsreleaseddate >= '07/01/2008') AND (o.new_fundsreleaseddate <= '06/30/2009') AND 
          (o.new_producttype <> 6) AND (o.new_applicationstatus = 11) OR
          (o.new_applicationstatus = 11) AND (o.new_startupdate >= '07/01/2008') AND 
          (o.new_startupdate <= '06/30/2009') AND (o.new_startupflag = 1)
--GROUP BY a.new_region, a.new_regionname, CASE WHEN DATEPART(m, o.new_fundsreleaseddate) < DATEPART(m, o.new_startupdate) THEN DATENAME(m, o.new_startupdate) ELSE DATENAME(m, o.new_fundsreleaseddate) end
) s
PIVOT(Count(regionID) for [fundsReleased] IN ([July], [August], [September], [October], [November], [December], [January], [February], [March], [April], [May], [June], [Total])) p
--ORDER BY regionID
 
GO

Open in new window

0
deadlyDevCommented:
Hmmm... just realized... u can take the total column out of the pivot query
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

ralmadaCommented:
Give this a try,
;with groupbyRegion as (
SELECT  [RegionID],
		[regionName], 
		[July] AS [Jul], 
		[August] AS [Aug], 
		[September] AS [Sep], 
		[October] AS [Oct], 
		[November] AS [Nov], 
		[December] AS [Dec], 
		[January] AS [Jan], 
		[February] AS [Feb], 
		[March] AS [Mar], 
		[April] AS [Apr], 
		[May] AS [May], 
		[June] AS [Jun], 
		[July] + [August] + [September] + [October] + [November] + [December] + [January] + [February] + [March] + [April] + [May] + [June] as [Total]
FROM
(
	SELECT  TOP (100) PERCENT 
			a.new_regionname AS regionName, 
			a.new_region AS regionID, 
			CASE WHEN DATEPART(yyyy, o.new_fundsreleaseddate) < DATEPART(yyyy, o.new_startupdate) THEN DATENAME(m, o.new_startupdate) 
				WHEN DATEPART(m, o.new_fundsreleaseddate) < DATEPART(m, o.new_startupdate) THEN DATENAME(m, o.new_startupdate) 
				ELSE DATENAME(m, o.new_fundsreleaseddate) END AS fundsReleased
	FROM         FilteredAccount a 
	INNER JOIN FilteredOpportunity o ON a.accountid = o.accountid
	WHERE     (o.new_fundsreleaseddate >= '07/01/2008') AND (o.new_fundsreleaseddate <= '06/30/2009') AND 
                      (o.new_producttype <> 6) AND (o.new_applicationstatus = 11) OR
                      (o.new_applicationstatus = 11) AND (o.new_startupdate >= '07/01/2008') AND 
                      (o.new_startupdate <= '06/30/2009') AND (o.new_startupflag = 1)
	order by a.new_region
)
s
PIVOT(Count(regionID) for [fundsReleased] IN ([July], [August], [September], [October], [November], [December], [January], [February], [March], [April], [May], [June])) p
)
select * from groupbyRegion
union all
select 0, 'Total', sum(Jul), sum(Aug), sum(Sep), sum(Oct), sum(Nov), sum(Dec), sum(Jan), sum(Feb), sum(Mar), sum(Apr), sum(May), sum(Jun), Sum(Total)
from groupbyRegion

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Steven O'NeillSolutions ArchitectAuthor Commented:
@deadlyDev - thanx for your solution however there appears to be a couple of items missing, namely the totals to each column and the correct sort order (I was looking to have this in region ID number as opposed to region alphabetically as it is right now).

@ralmada - many thanx for your solution. I did have a problem with the column RegionID as it was coming up with an Invalid Column name 'RegionID' error. I've altered the code slightly to add a new variable [regionCode] and this has the same value as [regionID] but as [regionID] is being used by the PIVOT I'm not sure if it can be used elsewhere. By adding this new field I can SORT on this within the SELECT statement and it produces what I'm after but I'm not sure if this is the most efficient way to do this. My results come back in about 2-3 seconds so there's no major delay just wanted to make sure this was the correct way to do it.

Thanx guys
;with groupbyRegion as (
SELECT  TOP (100) PERCENT --[regionCode],
		[regionName], 
		[July] AS [Jul], 
		[August] AS [Aug], 
		[September] AS [Sep], 
		[October] AS [Oct], 
		[November] AS [Nov], 
		[December] AS [Dec], 
		[January] AS [Jan], 
		[February] AS [Feb], 
		[March] AS [Mar], 
		[April] AS [Apr], 
		[May] AS [May], 
		[June] AS [Jun], 
		[July] + [August] + [September] + [October] + [November] + [December] + [January] + [February] + [March] + [April] + [May] + [June] as [Total]
FROM
(
	SELECT  TOP (100) PERCENT 
			a.new_regionname AS regionName, 
			a.new_region AS regionID, 
a.new_region AS regionCode, 
			CASE WHEN DATEPART(yyyy, o.new_fundsreleaseddate) < DATEPART(yyyy, o.new_startupdate) THEN DATENAME(m, o.new_startupdate) 
				WHEN DATEPART(m, o.new_fundsreleaseddate) < DATEPART(m, o.new_startupdate) THEN DATENAME(m, o.new_startupdate) 
				ELSE DATENAME(m, o.new_fundsreleaseddate) END AS fundsReleased
	FROM         FilteredAccount a 
	INNER JOIN FilteredOpportunity o ON a.accountid = o.accountid
	WHERE     (o.new_fundsreleaseddate >= '07/01/2008') AND (o.new_fundsreleaseddate <= '06/30/2009') AND 
                      (o.new_producttype <> 6) AND (o.new_applicationstatus = 11) OR
                      (o.new_applicationstatus = 11) AND (o.new_startupdate >= '07/01/2008') AND 
                      (o.new_startupdate <= '06/30/2009') AND (o.new_startupflag = 1)
	order by a.new_region
)
s
PIVOT(Count(regionID) for [fundsReleased] IN ([July], [August], [September], [October], [November], [December], [January], [February], [March], [April], [May], [June])) p
ORDER BY [regionCode]
)
select * from groupbyRegion
union all
select 'Total', sum(Jul), sum(Aug), sum(Sep), sum(Oct), sum(Nov), sum(Dec), sum(Jan), sum(Feb), sum(Mar), sum(Apr), sum(May), sum(Jun), Sum(Total)
from groupbyRegion

Open in new window

0
ralmadaCommented:
Well, I put an order by in line 32 of your code (order by a.new_region). So, the result should come sorted already. If not, then just remove that line and use the one you have in line 36.
0
Steven O'NeillSolutions ArchitectAuthor Commented:
Thanx for this. Works perfectly now.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.