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 | 10000 | 1 | 0 |
DOE | 10000 | 8 | 0 |
JON | 8000 | 9 | 1 |
JON | 5000 | 9 | 0 |
DOE | 10000 | 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 ?
3
Answers
The
GROUP BY
statement should list the non-aggregated column in theSELECT
.Looks like you want to group over
NAME
andSIG
so the query to do that would be:See a dbfiddle
Since you want
all the rows back
, and there is special columnid
, so I say you should not usegroup by
at all.Try this, I think you should use sub queries
For MySQL 8+ use window version of SUM():
fiddle