Include a column which sums up rows based on criteria

November 22, 2023

I have some code…

SELECT cEnrol, name, course, month, year, status, fee FROM table

which produces a table like this…

    | cEnrol | name | course | month | year |  status  | fee |
    |--------|------|--------|-------|------|----------|-----|
    |   1    | Aaaa | Zzzzzz |   01  | 2023 |  Active  | 100 |
    |   1    | Aaaa | Zzzzzz |   02  | 2023 |  Active  | 100 |
    |   1    | Aaaa | Zzzzzz |   03  | 2023 | Complete | 100 |
    |   2    | Bbbb | Yyyyyy |   01  | 2023 |  Active  | 200 |
    |   2    | Bbbb | Yyyyyy |   02  | 2023 |  Active  | 200 |
    |   2    | Bbbb | Yyyyyy |   03  | 2023 |  Active  | 200 |
    |   2    | Bbbb | Yyyyyy |   03  | 2023 | Complete | 200 |
    |   3    | Bbbb | Zzzzzz |   03  | 2023 |  Active  | 100 |
    |   3    | Bbbb | Zzzzzz |   03  | 2023 | Complete | 100 |

I would like to include a column that give the total of all fees for all rows with that cEnrol so that I get a table which looks like this…

    | cEnrol | name | course | month | year |  status  | fee | sumFees |
    |--------|------|--------|-------|------|----------|-----|---------|
    |   1    | Aaaa | Zzzzzz |   01  | 2023 |  Active  | 100 |   300   | 
    |   1    | Aaaa | Zzzzzz |   02  | 2023 |  Active  | 100 |   300   |
    |   1    | Aaaa | Zzzzzz |   03  | 2023 | Complete | 100 |   300   |
    |   2    | Bbbb | Yyyyyy |   01  | 2023 |  Active  | 200 |   800   |
    |   2    | Bbbb | Yyyyyy |   02  | 2023 |  Active  | 200 |   800   |
    |   2    | Bbbb | Yyyyyy |   03  | 2023 |  Active  | 200 |   800   |
    |   2    | Bbbb | Yyyyyy |   03  | 2023 | Complete | 200 |   800   |
    |   3    | Bbbb | Zzzzzz |   03  | 2023 |  Active  | 100 |   200   |
    |   3    | Bbbb | Zzzzzz |   03  | 2023 | Complete | 100 |   200   |

I have played around with nesting a SELECT statement but run into problems linking the sum back to each cEnrol.

Any ideas?

>Solution :

You can call the sum aggregator and partition by cEnrol.

Something like this :

SELECT
    cEnrol,
    name,
    course,
    month,
    year,
    status,
    fee,
    SUM(fee) OVER (PARTITION BY cEnrol) AS sumFees
FROM
    your_table_name;