SQL get the date with the least profit made-CodePudding

Currently I am working on a query to get the Date with the least made profit. The table, that I am using for this is this one:

The query I came up with finds the date with the least made profit, but doesnt show the SUM of the amount made in that day:

SELECT MIN(PRICE*QUANTITY) AS UMSATZ, ORDER_DATE
FROM FACT_ORDER
GROUP BY ORDER_DATE
ORDER BY UMSATZ
LIMIT 1;

So the result is:

Which query could show the SUM amount of the profit made that day in addition (not with an additional column)? Thank you very much in advance!

CodePudding user response：

Which query could show the SUM amount of the profit made that day in addition (not with an additional column)?

To get the total:

SELECT DISTINCT ORDER_DATE,
     SUM(PRICE*QUANTITY) OVER(PARTITION BY ORDER_DATE) AS UMSATZ 
FROM FACT_ORDER
QUALIFY (PRICE*QUANTITY) = MIN(PRICE*QUANTITY) OVER(PARTITION BY ORDER_DATE);

CodePudding user response：

You can achieve this with a self join.

select umsatz, a.order_date, sumUmsatz from (
    select 
    min(price*quantity) as umsatz, order_date
    from FACT_ORDER 
    group by datum order by min(price*quantity)asc
)a inner join (
    select sum(price*quantity) as sumUmsatz, order_date from 
    FACT_ORDER group by order_date
) b on a.order_date = b.order_date
where rownum = 1;