Calculate Median on Sql

Question

I am very new to SQL. I am trying to use the code below and it keeps giving me the following error -

Msg The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

Incorrect syntax near '2'.

Please can someone help - where am I going wrong?

I am just trying to calculate median.

Could someone please suggest amendment to this code as I have picked up someone else's work.

Any help will be greatly appreciated.

SELECT
    TOP (100) PERCENT Name, Sales Median
FROM
    (SELECT 
         a1.Name, a1.Sales, COUNT(a1.Sales) Rank
     FROM          
         PSE.vw_EM_Data a1, PSE.vw_EM_Data a2
     WHERE      
         a1.Sales < a2.Sales OR
         (a1.Sales = a2.Sales AND a1.Name <= a2.Name)
     GROUP BY 
         a1.Name, a1.Sales
     ORDER BY 
         a1.Sales DESC) a3
WHERE 
    Rank = (SELECT (COUNT(*) + 1) DIV 2 FROM Total_Sales);
WHERE     
    (Name LIKE 'John%') 
    AND (DISPOSAL = '1') 
    AND (DATE BETWEEN CONVERT(DATETIME, '2010-04-01 00:00:00', 102) AND CONVERT(DATETIME, '2011-03-30 00:00:00', 102))

Answer 1

You problem is from the order by with in the subselect a3

Why not rather have a look at using Ranking Functions (Transact-SQL)

Answer 2

Couple things to point out:

1) I would never use the "old-style" JOIN syntax that you use in your inner SELECT - it can easily lead to unwanted cartesian products - as it does in your case (though I'm not sure whether that's wanted or not....)

So instead of:

FROM          
   PSE.vw_EM_Data a1, PSE.vw_EM_Data a2

I would recommend to use the proper ANSI JOIN syntax

FROM          
   PSE.vw_EM_Data a1  
INNER JOIN           -- or possibly: LEFT OUTER JOIN or even FULL OUTER JOIN
   PSE.vw_EM_Data a2 ON a1.Sales = a2.Sales

which makes it much clearer what it is you really want, and avoids any unnecessary problems.

2) In your second WHERE clause, you first of all don't need all those parenthesis, really - and most of all, you definitely shouldn't be converting a DATE or DATETIME column into a string to then compare it - so instead of this:

WHERE     
    (Name LIKE 'John%') 
    AND (DISPOSAL = '1') 
    AND (DATE BETWEEN CONVERT(DATETIME, '2010-04-01 00:00:00', 102) AND CONVERT(DATETIME, '2011-03-30 00:00:00', 102))

use this instead:

WHERE     
    Name LIKE 'John%'
    AND DISPOSAL = '1'
    AND [DATE] BETWEEN '2010-04-01T00:00:00' AND '2011-03-30T00:00:00'

and I would also try to avoid calling a column by a name that's a keyword in SQL like DATE - you'll have to use square brackets around it to make things clear to SQL Server.