I have the below Table
| NAME | VAL | ID | SIG |
|---|---|---|---|
| DOE | 4000 | 1 | 0 |
| DOE | 5000 | 8 | 0 |
| JON | 8000 | 9 | 1 |
| JON | 5000 | 9 | 0 |
| DOE | 1000 | 4 | 0 |
I need to group by columns NAME and SIG and create another column SUMVAL whose SUM is obtained by a Group of NAME and SIG values as in the above example we have three rows with the Name DOE and SIG value as 0
so after initial grouping the output will be below , DOE will have one record and JON will have two records having two SIG Values
| NAME | SUMVAL | SIG |
|---|---|---|
| DOE | 10000 | 0 |
| JON | 8000 | 1 |
| JON | 5000 | 0 |
I was able to get the above output , but however I want the below output, where I need to get the SUMVAL spread for other rows too and show the complete view with all the records
| NAME | SUMVAL | ID | SIG |
|---|---|---|---|
| DOE | 1000 | 1 | 0 |
| DOE | 1000 | 8 | 0 |
| JON | 8000 | 9 | 1 |
| JON | 5000 | 9 | 0 |
| DOE | 1000 | 4 | 0 |
I tried the below command and half way through, but couldn’t get the rest working
select sum(VAL) as SUMVAL,ID
GROUP BY SIG,NAME
How do I get all the rows back again like in the above output ?