Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SUM values in a column but show all the rows after group by

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 ?

Tags:

3

Answers


  1. 0

    The GROUP BY statement should list the non-aggregated column in the SELECT.

    Looks like you want to group over NAME and SIG so the query to do that would be:

    select NAME, sum(val), SIG
from data
group by NAME, SIG

    See a dbfiddle

    Login or Signup to reply.
  2. 0

    Since you want all the rows back, and there is special column id, so I say you should not use group by at all.

    Try this, I think you should use sub queries

    select 
name, 
(select sum(val) from your_table as i where i.name = o.name and i.sig=o.sig) as sumval, 
id, 
sig 
from your_table as o;
    Login or Signup to reply.
  3. 0

    For MySQL 8+ use window version of SUM():

    SELECT name, SUM(val) OVER (PARTITION BY name, sig) sumval, id, sig
FROM test;
    name sumval id sig
    DOE 10000 1 0
    DOE 10000 8 0
    DOE 10000 4 0
    JON 5000 9 0
    JON 8000 9 1

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search