how to find maximum of sum of number using if else in procedure in sap hana sql

Question

I want to list out the product which has highest sales amount on date wise. note: highest sales amount in the sense max(sum(sales_amnt)... by using if or case In the procedure in sap hana SQL....

I did this by using with the clause :

/--------------------------CORRECT ONE ----------------------------------------------/

WITH ranked AS 

(
  SELECT Dense_RAnk() OVER (ORDER BY SUM("SALES_AMNT"), "SALES_DATE", "PROD_NAME") as rank, 
  SUM("SALES_AMNT") AS Amount, "PROD_NAME",count(*), "SALES_DATE" FROM "KABIL"."DATE" 
  GROUP BY "SALES_DATE", "PROD_NAME"
)
SELECT "SALES_DATE", "PROD_NAME",Amount
FROM ranked
WHERE rank IN ( select MAX(rank) from ranked group by "SALES_DATE")
ORDER BY "SALES_DATE" DESC;

this is my table

Answer 1

You can not use IF along with SELECT statement. Note that, you can achieve most of boolean logics with CASE statement syntax

In select, you are applying it over a column and your logic will be executed as many as times the count of result set rows. Hence , righting an imperative logic is not well appreciated. Still, if you want to do the same, create a calculation view and use intermediate calculated columns to achieve what you are expecting .

Answer 2

try this... i got an answer ...

select "SALES_DATE","PROD_NAME",sum("SALES_AMNT")
from "KABIL"."DATE"
group by "SALES_DATE","PROD_NAME"
having (SUM("SALES_AMNT"),"SALES_DATE") IN (select 
                                            MAX(SUM_SALES),"SALES_DATE"
                                            from (select SUM("SALES_AMNT") 
                                            as 
                                           SUM_SALES,"SALES_DATE","PROD_NAME" 
                                           from "KABIL"."DATE"
                                           group by "SALES_DATE","PROD_NAME" 
                                            )                                                   
                                           group by "SALES_DATE");