skip to Main Content

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


  1. 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. 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. 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