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

Mysql – Group By on certain condition but should display all rows in the original data

I have the below Data

NAME AMT EID Tran
John 4000 1 T1
Mary 5000 2 T2
John 8000 1 T3
Willi 5000 3 T4
Mary 1000 2 T5

I wanted to group by Name, EID and sum the AMT column but still show all the row values as in the below output

NAME AMT EID Tran Total Amt
John 4000 1 T1 12000
Mary 5000 2 T2 6000
John 8000 1 T3 12000
Willi 5000 3 T4 5000
Mary 1000 2 T5 6000

I have tried this , but it doesn’t work and I am using MySQL 5.7 and it doesn’t support LEAD and LAG either.

select SUM(AMT) as TotalAmt, * from mytable
group by NAME,EID

How do I get the output below ?

NAME AMT EID Tran Total Amt
John 4000 1 T1 12000
Mary 5000 2 T2 6000
John 8000 1 T3 12000
Willi 5000 3 T4 5000
Mary 1000 2 T5 6000
Tags:

2

Answers


  1. 0

    The simple way is to upgrade your MySQL version and then use a window sum:

    SELECT
  NAME,
  AMT,
  EID,
  TRan,
  SUM(AMT) OVER(PARTITION BY NAME) AS `Total Amt`
FROM yourtable;

    If you can’t upgrade, you can use a subquery to fetch the sum for the current Name:

    SELECT
  y_main.NAME,
  y_main.AMT,
  y_main.EID,
  y_main.TRan,
  (SELECT SUM(y_sub.AMT) FROM yourtable y_sub 
    WHERE y_sub.NAME = y_main.NAME) AS `Total Amt`
FROM yourtable y_main;

    Upgrading and using a window sum is far simpler and faster because it prevents the tabe will queried twice. That’s the reason why window functions have been introduced.

    On this sample fiddle with your data in MySQL 8.0, both queries produce the expected result. If you change the version to 5.7 in the fiddle and run it again, the window sum won’t work anymore because it’s not supported there.

    Login or Signup to reply.
  2. 0

    As suggested by @jonas right approach is to upgrade mysql and using analytical window function.

    Just wanted to make a general point that for such scenarios where you want to group data on certain column and still want to view all the columns, over clause with partition by is the best solution. Partition By acts as a group by while still retaining all the columns.

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

Back To Top
Search