Hi, I have meet this question:
I want to use all the history data to find which one is the most popular good in the supermarket, and then I will return the sales of that good in the last 30 days:
my SQL query is:
select sum(case when STATUS=1 then SELL_AMT end)
from SALES_HISTORY
where $__timeFilter(CRTD_TS)
group by concat(CATEGORY, ‘-’, GOOD_NAME)
order by sum(case when STATUS=1 then SELL_AMT end) DESC
limit 1;
(Here STATUS=1 means the sales is completed)
But it seems the $__timeFilter is a global variable, that my “order by” command can only do the sum within the last 30 days, instead of all the history data.
Can anyone help me with this? Thank you