I want to calculate the sum of the orders based on the flag. Sample table:
+--------------+---------------+---------------+
| Order | Flag | Amount |
+--------------+---------------+---------------+
| Order1 | Yes | 500 |
| Order1 | Yes | 325 |
| Order2 | Yes | 799 |
| Order2 | No | 550 |
| Order2 | Yes | 675 |
| Order3 | No | 800 |
+--------------+---------------+---------------+
I want to create 2 new columns.
One column is the total amount of order and another one is the total amount of order where the flag is 'yes'.
The wanted result from the example table above:
+--------------+---------------+---------------+---------------+---------------+
| Order | Flag | Amount | TotalAmount | Yes_amount |
+--------------+---------------+---------------+---------------+---------------+
| Order1 | Yes | 500 | 825 | 825 |
| Order1 | Yes | 325 | 825 | 825 |
| Order2 | Yes | 799 | 2024 | 1474 |
| Order2 | No | 550 | 2024 | 1474 |
| Order2 | Yes | 675 | 2024 | 1474 |
| Order3 | No | 800 | 800 | 0 |
+--------------+---------------+---------------+---------------+---------------+
I have tried the below code to get TotalAmount column:
SUM(AMOUNT) OVER (PARTITION BY ORDER) AS TOTAL_AMOUNT.
Can someone help me with the Yes_amount column?