Link to home
Start Free TrialLog in
Avatar of gmollineau
gmollineauFlag for Trinidad and Tobago

asked on

How do I use a WHERE within ORDER BY in SQL

I have an SQL Script where I wouant to ORDER By the Sum of a field where the year is = tp a parameter.


The Table has three (3) years in it, but I want to sort by the sum of the values in only one of the years. The other year will just be data in the record. 


The scripts is: 

SELECT
     "dbo"."Price_Point_Sales_Summary"."ITEM_ID",
     "dbo"."Price_Point_Sales_Summary"."ITEM_DESCRIPTION",
     "dbo"."Price_Point_Sales_Summary"."COMPANY_ID",
     "dbo"."Price_Point_Sales_Summary"."COMPANY_NAME",
    sum( "dbo"."Price_Point_Sales_Summary"."EXTENDED_SALES") as EXTENDED_SALES,
     "dbo"."Price_Point_Sales_Summary"."PRICE_POINT_CODE",
     "dbo"."Price_Point_Sales_Summary"."Price_Point_Desc",
     "dbo"."Price_Point_Sales_Summary"."YEAR"
 
FROM
     "dbo"."Price_Point_Sales_Summary"

Where  "dbo"."Price_Point_Sales_Summary"."YEAR" between 2021 -2 and 2021
and      "dbo"."Price_Point_Sales_Summary"."FISCAL_PERIOD" between 1 and 11


GROUP BY

   "dbo"."Price_Point_Sales_Summary"."ITEM_ID",
     "dbo"."Price_Point_Sales_Summary"."ITEM_DESCRIPTION",
     "dbo"."Price_Point_Sales_Summary"."COMPANY_ID",
     "dbo"."Price_Point_Sales_Summary"."COMPANY_NAME",
   
     "dbo"."Price_Point_Sales_Summary"."PRICE_POINT_CODE",
     "dbo"."Price_Point_Sales_Summary"."Price_Point_Desc",
     "dbo"."Price_Point_Sales_Summary"."YEAR"

ORDER BY
     sum( "dbo"."Price_Point_Sales_Summary"."EXTENDED_SALES") DESC


 The ORDER BY is where I want to be able to sort Descending WHERE  YEAR = 2021 


Can anyone assist


Regards


Gerald

Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Not sure you you don't want ALL the records sorted.

But, it is your requirement.  Possibly a case statement

Something like:
select ...
ORDER BY
     case when year=2021 then sum( "dbo"."Price_Point_Sales_Summary"."EXTENDED_SALES") end DESC

Open in new window

Quick fiddle:
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=f6cae4daaaa90e33cf502562d5c84391
ASKER CERTIFIED SOLUTION
Avatar of ste5an
ste5an
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of gmollineau

ASKER

both helpful
both helpful