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
 Pivot Order by Value Desc

Author  Topic 

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-07-10 : 09:04:13
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

52326 Posts

Posted - 2013-07-10 : 09:14:26
[code]
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
[/code]

------------------------------------------------------------------------------------------------------
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 - 2013-07-10 : 09:16:58
visakh16
Your knowledge astonishes me
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-10 : 09:19:27
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

30421 Posts

Posted - 2013-07-10 : 11:26:16
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
   

- Advertisement -