I have a SQL table like this:
| ID | Level | Type | Value |
|---|---|---|---|
| A | ZZ | 1 | 20 |
| A | YY | 1 | 10 |
| C | XX | 1 | 30 |
| B | ZZ | 1 | 40 |
| B | XX | 1 | 60 |
| C | YY | 1 | 100 |
| D | WW | 2 | 70 |
| D | VV | 2 | 90 |
For each ‘Level’, if its ‘Type’ = 1, I want to sum all the ‘Value’ associated with IDs that includes that level, even if the ‘Value’ isn’t associated directly with that specific level. For example, for level ZZ, it would sum all values associated with IDs A and B, because these two IDs include level ZZ: ID A Level ZZ + ID A Level YY + ID B Level ZZ + ID B Level XX. (more details below).
My expected outcome would be a table like this:
| Level | Value |
|---|---|
| ZZ | 130 |
| YY | 160 |
| XX | 230 |
The table is calculates as follows:
- Level ZZ: sum all values related to IDs A and B [20+10+40+60=130]
- Level YY: sum all values related to IDs A and C [20+10+30+100=160]
- Level XX: sum all values related to IDs B and C [30+40+60+100=230]
- Levels WW and VV would not be included as they are type 2.
Thanks!
user26259727 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Start with getting the sums per ID, from thereon it gets easy-peasy.
with
id_sums as
(
select id, sum(value) as sum_value
from mytable
where type = 1
group by id
)
select t.levl, sum(s.sum_value) as total
from mytable t
join id_sums s on s.id = t.id
group by t.levl
order by t.levl;
The same can be done with a window function which you may or may not find more readable:
with
with_sums as
(
select t.*, sum(value) over (partition by id) as id_sum_value
from mytable t
where type = 1
)
select levl, sum(id_sum_value) as total
from with_sums
group by levl
order by levl;
(I’ve renamed your level column levl here, because LEVEL is a reserved word in Oracle.)
Demo: https://dbfiddle.uk/D_ylLGc9