Table [Revenue Raw Data] has the following columns:

Table [Targets Raw Data] has the following columns:

Table yt_Calendar_lookup has the following columns:

Now the question is:

Which account number is performing the best in terms of Revenue versus Target in the Fiscal Year 2021?

Initially this is what I have tried:

SELECT [Revenue Raw Data].Account_No, 
[Total Revenue in 2021], 
[Total Target in 2021]
FROM
(SELECT Account_No, SUM(Revenue) AS 'Total Revenue in 2021' 
FROM [Revenue Raw Data]
 WHERE Account_No IN 
(SELECT DISTINCT Account_No 
 FROM yt_Calendar_lookup WHERE 
 Fiscal_Year = 'FY21')
 GROUP BY Account_No) [Revenue Raw Data]
 INNER JOIN
(SELECT Account_No, 
 SUM(Target) AS 'Total Target in 2021' 
  FROM [Targets Raw Data]
 WHERE Account_No IN 
(SELECT DISTINCT Account_No 
 FROM yt_Calendar_lookup 
 WHERE Fiscal_Year = 'FY21')
 GROUP BY Account_No) [Targets Raw Data]
 ON [Revenue Raw Data].Account_No = [Targets Raw Data].Account_No

And I got the following result:

With the next query, I was trying to display the same result but this time I want to display both SUM of Revenue and SUM of Target in Descending Order.

And this is what I have tried:

SELECT [Revenue Raw Data].Account_No, 
[Total Revenue in 2021], 
[Total Target in 2021]
FROM
(SELECT Account_No, 
 SUM(Revenue) AS 'Total Revenue in 2021' 
 FROM [Revenue Raw Data]
 WHERE Account_No IN 
 (SELECT DISTINCT Account_No 
 FROM yt_Calendar_lookup 
 WHERE Fiscal_Year = 'FY21')
 GROUP BY Account_No
 ORDER BY SUM(Revenue) DESC
 ) 
 [Revenue Raw Data]
 INNER JOIN
(SELECT Account_No, 
 SUM(Target) AS 'Total Target in 2021' 
 FROM [Targets Raw Data]
 WHERE Account_No IN 
 (SELECT DISTINCT Account_No 
 FROM yt_Calendar_lookup 
 WHERE Fiscal_Year = 'FY21')
 GROUP BY Account_No
 ORDER BY SUM(Target) DESC) 
 [Targets Raw Data]
 ON [Revenue Raw Data].Account_No 
  = [Targets Raw Data].Account_No

And I received the following error message:

I even tried adding ORDER BY SUM(Revenue) DESC and ORDER BY SUM(Target) DESC outside the Subquery, and still it is showing error.

How can this issue be solved ?

CodePudding user response：

Does this work? I think the ORDER BY should be outside of the subquery, so it's the output of the main SELECT you're ordering. Ie no ORDER within brackets, but ORDER BY [Total Revenue in 2021] DESC, [Total Target in 2021] at the end

SELECT [Revenue Raw Data].Account_No, 
[Total Revenue in 2021], 
[Total Target in 2021]
FROM
(SELECT Account_No, 
 SUM(Revenue) AS 'Total Revenue in 2021' 
 FROM [Revenue Raw Data]
 WHERE Account_No IN 
 (SELECT DISTINCT Account_No 
 FROM yt_Calendar_lookup 
 WHERE Fiscal_Year = 'FY21')
 GROUP BY Account_No
  ) 
 [Revenue Raw Data]
 INNER JOIN
(SELECT Account_No, 
 SUM(Target) AS 'Total Target in 2021' 
 FROM [Targets Raw Data]
 WHERE Account_No IN 
 (SELECT DISTINCT Account_No 
 FROM yt_Calendar_lookup 
 WHERE Fiscal_Year = 'FY21')
 GROUP BY Account_No
 ) 
 [Targets Raw Data]
 ON [Revenue Raw Data].Account_No 
  = [Targets Raw Data].Account_No

ORDER BY [Total Revenue in 2021] DESC, [Total Target in 2021] 

CodePudding user response：

A couple of things to think about here:

1. You can't put ORDER BY in a sub-query. Order by is used once at the end of a query
2. Even if you could, that doesn't make sense when you're using the sub-query as the values list for an IN predicate
3. Generally I would advise against using sub-queries as the value list for an IN predicate. I believe you'll get better performance if you achieve the same filtering using an INNER JOIN
4. Sorting two independent measurements of the same (single) grouping variable descending at the same time doesn't make sense - the max value for one measurement might be account 1, but account 1 might not have the max value for the other measurement. What then?
5. If you are joining on account_no throughout, you don't need to apply the FY21 filter twice

In fact, I think your query can be simplified along these lines:

SELECT  raw.Account_No, 
        SUM(raw.Revenue) AS 'Total Revenue in 2021',
        target.[Total Target in 2021]
 FROM [Revenue Raw Data] raw
      INNER JOIN (SELECT Account_No, SUM(Target) AS 'Total Target in 2021' 
                  FROM [Targets Raw Data]
                  GROUP BY account_no) target ON raw.account_no = target.account_no
      INNER JOIN (SELECT DISTINCT Account_No 
                  FROM yt_Calendar_lookup 
                  WHERE Fiscal_Year = 'FY21') fltr ON raw.account_no = fltr.account_no
GROUP BY raw.account_no
ORDER BY SUM(raw.revenue);

Again, when you only have one row per account ID, you either sort by one measure or the other. Sorting by both will be the same as sorting by the first one listed. Unless you expect multiple accounts with the same value in the first sort key, but different values int he second sort key. In which case:

ORDER BY SUM(raw.revenue), target.[Total Target in 2021];