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

Get the MIN(date) across all records BUT also getting SUM for each type – Mysql

I have the following data delimited by COMMA.

balance_date,name,wallet_type,available_balance

2020-05-07 13:28:59,wokalap,debit,12345
2020-06-02 08:07:18,wokalap,credit,1116
2020-06-02 08:07:18,wokalap,crypto,113
2020-05-06 59:10:05,wokalap,debit,55795
2020-06-07 08:07:18,wokalap,credit,1448
2020-06-05 08:07:18,wokalap,crypto,546

I want to get the SUM for each wallet_type but I also would ONLY want to get the earliest date across all the records and not for each wallet_type.

I am expecting this as a result:

balance_date name wallet_type available_balance
2020-05-06 59:10:05 wokalap debit 68140
2020-05-06 59:10:05 wokalap credit 2564
2020-05-06 59:10:05 wokalap crypto 659

Please help figure out the correct script to produce such results. I am using mysql workbench for this.

Tags:

5

Answers


  1. 0

    Using a subquery to find the minimum date, we can try the following simple aggregation:

    SELECT (SELECT MIN(balance_date) FROM yourTable) AS balance_date,
       name,
       wallet_type,
       SUM(available_balance) AS total_balance
FROM yourTable
GROUP BY
    name,
    wallet_type;
    Login or Signup to reply.
  2. 0

    I want to get the SUM for each wallet_type but I also would ONLY want
    to get the earliest date across all the records and not for each
    wallet_type.

    This can be written as follows :

     select 
  subq.min_date as balance_date, 
  t.name, 
  t.wallet_type, 
  sum(t.available_balance) as available_balance
from 
  mytable t 
  join (
    select min(balance_date) as min_date from mytable
  ) subq 
group by 
  t.name, 
  t.wallet_type
  order by available_balance desc;

    This gives me correct output as :

    -----------------------------------------------------------------
|balance_date       | name   | wallet_type|  available_balance  |
-----------------------------------------------------------------
|2020-05-06 59:10:05| wokalap| debit      |  68140              |
|2020-05-06 59:10:05| wokalap| credit     |  2564               |
|2020-05-06 59:10:05| wokalap| crypto     |  659                |
-----------------------------------------------------------------

    Here is a demo using SQLFIDDLE

    Updates based on user input :

    When user inserts more sample data like this :

    insert into mytable values 
('2020-05-07 13:28:59','wokalap','debit','12345'),
('2020-06-02 08:07:18','wokalap','credit','1116'),
('2020-06-02 08:07:18','wokalap','crypto','113'),
('2020-05-06 59:10:05','wokalap','debit','55795'),
('2020-06-07 08:07:18','wokalap','credit','1448'),
('2020-06-05 08:07:18','wokalap','crypto','546'),
('2020-05-07 13:28:59','pakalow','debit','12345'),
('2020-06-02 08:07:18','pakalow','credit','1116'),
('2020-01-01 08:07:18','pakalow','crypto','113'),
('2020-05-06 59:10:05','pakalow','debit','55795'),
('2020-06-07 08:07:18','pakalow','credit','1448'),
('2020-06-05 08:07:18','pakalow','crypto','546');

    Expected output :

    would want is for the script to also get the MIN(date) for each new
    name being added on the table.

    The query is as below :

    select 
  subq.min_date as balance_date, 
  t.name, 
  t.wallet_type, 
  sum(t.available_balance) as available_balance
from 
  mytable t 
  join (
    select name, min(balance_date) as min_date from mytable
    group by name
  ) subq 
  on t.name = subq.name
group by 
  t.name, 
  t.wallet_type
order by available_balance desc;

    Here is a demo using SQLFIDDLE

    Login or Signup to reply.
  3. 0

    Below is a Simple Code that executes in MySQL. Please change it according to your database.

    SELECT MIN(balance_date), ax.* FROM your_table my 
JOIN (
SELECT person_name, wallet_type, SUM(available_bal) 
FROM your_table GROUP BY wallet_type ) ax 
ON my.person_name = ax.person_name GROUP BY ax.person_name, ax.wallet_type;

    No need for windows functions that are only supported in 8.0+ versions, the above query executes in any MySQL version.

    Login or Signup to reply.
  4. 0

    You could try to use sub-queries

    SELECT
  (SELECT MIN(balance_date) 
   FROM mytable d1 WHERE d1.name = d.name) AS min_date, 
  name,
  wallet_type,
  SUM(available_balance) AS available_balance
FROM mytable d
GROUP BY name, wallet_type

    Or if you are using MySQL version 8.0+, you could try window function

    SELECT
  DISTINCT
  MIN(balance_date) OVER(PARTITION BY name) AS min_date, 
  name,
  wallet_type,
  SUM(available_balance) OVER(PARTITION BY name, wallet_type) AS available_balance 
FROM mytable

    See demo here

    Login or Signup to reply.
  5. 0

    We can do this simply without joins, using window functions and aggregation (assuming MySQL 8.0):

    select min(min(balance_date)) over() as min_balance_date,
    name, 
    wallet_type, 
    sum(available_balance) available_balance
from mytable
group by name, wallet_type

    In essence that’s an aggregation query by name and wallet type, where the balance is summed. Then, we use window functions to compute the earliest date over all aggregates.

    This scans the table only one, hence it is more efficient than options involving joins for example.

    Demo on DB Fiddle:

    min_balance_date name wallet_type available_balance
    2020-05-06 59:10:05 wokalap debit 68140
    2020-05-06 59:10:05 wokalap credit 2564
    2020-05-06 59:10:05 wokalap crypto 659
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search