SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Pivot Order by Value Desc
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

masond
Constraint Violating Yak Guru

447 Posts

Posted - 07/10/2013 :  09:04:13  Show Profile  Reply with Quote
Hi Guys and Girls

This seems such a daft question, but this is my query (see below), all i want to do is order grand total desc


SELECT ParentID,
Parent_Name,
[EU],
[INT],
[UK],
ISNULL([EU],0)
+ISNULL([INT],0)
+ISNULL([UK],0) as 'Grand Total'
FROM
(SELECT ParentID,
Parent_Name,
Dim_Interchange_Tier_2.Region,
SUM(Fact_Financial_History_2.hst_sales_amt_R12) AS [Turnover]
FROM
fdms.dbo.Fact_Financial_History_2
INNER JOIN fdms.dbo.Dim_Interchange_Tier_2
ON Fact_Financial_History_2.hst_prod_code + '-' + Fact_Financial_History_2.hst_plan_code = Dim_Interchange_Tier_2.Plan_Code
inner join fdms.dbo.Dim_Outlet on hst_merchnum =FDMSAccountNo_First9
where ParentID in (select ParentID from #top)
GROUP BY
Dim_Outlet.ParentID,
Dim_Outlet.Parent_Name,
Dim_Interchange_Tier_2.region
)as p
PIVOT
( MAX([Turnover]) FOR Region IN ([EU],[INT],[UK])) as pvt

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/10/2013 :  09:14:26  Show Profile  Reply with Quote

SELECT ParentID,
Parent_Name,
[EU],
[INT], 
[UK], 
ISNULL([EU],0)
+ISNULL([INT],0)
+ISNULL([UK],0) as 'Grand Total'
FROM
(SELECT ParentID,
Parent_Name,
Dim_Interchange_Tier_2.Region,
SUM(Fact_Financial_History_2.hst_sales_amt_R12) AS [Turnover]
FROM 
fdms.dbo.Fact_Financial_History_2 
INNER JOIN fdms.dbo.Dim_Interchange_Tier_2
ON Fact_Financial_History_2.hst_prod_code + '-' + Fact_Financial_History_2.hst_plan_code = Dim_Interchange_Tier_2.Plan_Code
inner join fdms.dbo.Dim_Outlet on hst_merchnum =FDMSAccountNo_First9
where ParentID in (select ParentID from #top)
GROUP BY 
Dim_Outlet.ParentID,
Dim_Outlet.Parent_Name,
Dim_Interchange_Tier_2.region
)as p
PIVOT
( MAX([Turnover]) FOR Region IN ([EU],[INT],[UK])) as pvt
ORDER BY [Grand Total] DESC


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 07/10/2013 :  09:16:58  Show Profile  Reply with Quote
visakh16
Your knowledge astonishes me
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/10/2013 :  09:19:27  Show Profile  Reply with Quote
quote:
Originally posted by masond

visakh16
Your knowledge astonishes me


I'm myself astonished by knowledge of quality geeks here

I'm just a humble follower of them

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30116 Posts

Posted - 07/10/2013 :  11:26:16  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Isn't this enough?
SELECT		o.ParentID,
		o.Parent_Name,
		SUM(CASE WHEN it.Region = 'EU' THEN fh.hst_sales_amt_R12 ELSE 0 END) AS EU,
		SUM(CASE WHEN it.Region = 'INT' THEN fh.hst_sales_amt_R12 ELSE 0 END) AS [INT],
		SUM(CASE WHEN it.Region = 'UK' THEN fh.hst_sales_amt_R12 ELSE 0 END) AS UK,
		SUM(fh.hst_sales_amt_R12) AS [Grand Total]
FROM		fdms.dbo.Fact_Financial_History_2 AS fh
INNER JOIN	fdms.dbo.Dim_Interchange_Tier_2 AS it ON it.Plan_Code = fh.hst_prod_code + '-' + fh.hst_plan_code
INNER JOIN	fdms.dbo.Dim_Outlet AS o ON {Table alias here}.hst_merchnum = {Table alias here}.FDMSAccountNo_First9
INNER JOIN	#Top AS t ON t.ParentID = o.ParentID
GROUP BY	o.ParentID,
		o.Parent_Name;



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000