Avatar of gmollineau
gmollineau
Flag 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

SQL

Avatar of undefined
Last Comment
gmollineau

8/22/2022 - Mon
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
ste5an

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
gmollineau

ASKER
both helpful
gmollineau

ASKER
both helpful
Your help has saved me hundreds of hours of internet surfing.
fblack61