Mysql query – updating each record in table with sum, count of previous records

MySQLmysql-5.7

We have a mysql transaction table where every run registration attempt (paystatus = completed, pending, incomplete, etc) is recorded with a bunch of other data. I want to create additional columns in the same table and update them with aggregate values calculated from previous rows (e.g – same runner can come to multiple runs, get aggregate of previous run records for same person into current record).

Unique columns –

regid – primary key
combination of email+firstname (not exactly accurate but we use this
assumption for a unique person)

Source table structure (sqlfiddle – http://sqlfiddle.com/#!9/ff191d/6) and example data

source example data - 
+---------------------------------------------------------------------------------------------------------------+
| regid | registeredon | paystatus | firstname |    email   | amountpaid | totalruns | totalamount | influencer |
+---------------------------------------------------------------------------------------------------------------+
|  a2   |  25-05-2018  | completed |    jay    |    j@x.c   |    100     |     0     |      0      |     0      |
|  z2   |  14-03-2018  | completed |    jay    |    j@x.c   |    100     |     0     |      0      |     0      |
|  y2   |  23-02-2018  | completed |  vijay    |    v@z.c   |    150     |     0     |      0      |     0      |
|  y6   |  21-02-2018  |  pending  |  vijay    |    v@z.c   |     0      |     0     |      0      |     0      |
|  x2   |  14-01-2018  | completed |  vijay    |    v@z.c   |    100     |     0     |      0      |     0      |
|  x6   |  11-01-2018  |  pending  |  vijay    |    v@z.c   |    100     |     0     |      0      |     0      |
|  a1   |  23-05-2018  | completed |   ajay    |    a@x.c   |    120     |     0     |      0      |     0      |
|  a5   |  21-05-2018  |  pending  |   ajay    |    a@x.c   |     0      |     0     |      0      |     0      |
|  z1   |  04-03-2018  | completed |   ajay    |    a@x.c   |    100     |     0     |      0      |     0      |
|  z5   |  02-03-2018  |  pending  |   ajay    |    a@x.c   |     0      |     0     |      0      |     0      |
|  y1   |  03-02-2018  | completed |   ajay    |    a@x.c   |    150     |     0     |      0      |     0      |
|  x1   |  02-01-2018  | completed |   ajay    |    a@x.c   |    100     |     0     |      0      |     0      |
|  x5   |  01-01-2018  |  pending  |   ajay    |    a@x.c   |     0      |     0     |      0      |     0      |
+---------------------------------------------------------------------------------------------------------------+

The aggregate value columns to be calculated are (count only successful registrations for all i.e paystatus = completed)

totalruns – Total runs runner has participated in, including current one.
totalamount – Sum of all amount runner has paid for current and previous runs. Total amount runner has paid till that run including current row.
influencer – count of successful registrations for that email (not including firstname like above)

Expected result data after calculations

+---------------------------------------------------------------------------------------------------------------+
| regid | registeredon | paystatus | firstname |    email   | amountpaid | totalruns | totalamount | influencer |
+---------------------------------------------------------------------------------------------------------------+
|  a2   |  25-05-2018  | completed |    jay    |    j@x.c   |    100     |     2     |     200     |     1      |
|  z2   |  14-03-2018  | completed |    jay    |    j@x.c   |    100     |     1     |     100     |     1      |
|  y2   |  23-02-2018  | completed |  vijay    |    v@z.c   |    150     |     2     |     250     |     2      |
|  y6   |  21-02-2018  |  pending  |  vijay    |    v@z.c   |     0      |     1     |     100     |     1      |
|  x2   |  14-01-2018  | completed |  vijay    |    v@z.c   |    100     |     1     |     100     |     1      |
|  x6   |  11-01-2018  |  pending  |  vijay    |    v@z.c   |    100     |     1     |     100     |     0      |
|  a1   |  23-05-2018  | completed |   ajay    |    a@x.c   |    120     |     4     |     470     |     4      |
|  a5   |  21-05-2018  |  pending  |   ajay    |    a@x.c   |     0      |     3     |     350     |     3      |
|  z1   |  04-03-2018  | completed |   ajay    |    a@x.c   |    100     |     3     |     350     |     3      |
|  z5   |  02-03-2018  |  pending  |   ajay    |    a@x.c   |     0      |     2     |     250     |     2      |
|  y1   |  03-02-2018  | completed |   ajay    |    a@x.c   |    150     |     2     |     250     |     2      |
|  x1   |  02-01-2018  | completed |   ajay    |    a@x.c   |    100     |     1     |     100     |     1      |
|  x5   |  01-01-2018  |  pending  |   ajay    |    a@x.c   |     0      |     0     |      0      |     0      |
+---------------------------------------------------------------------------------------------------------------+

Explanation – totalamount, totalruns, influencer for regid x5 is 0 because it was not a successful registration (paystatus = pending). x1 aggregate values are of the same record as it is the first successful registration. y1 aggregate values are sum of values for x1 and y1. z5 has aggregate values of previous successful registration, instead this can be left at 0 too (if update is run only for paystatus=completed).

I have been able to get to this query with the help of a friend, but it is not correct (sum of all records with email+firstname combination is shown instead of only older rows with that combination) –

    SELECT 
t1.regid, registeredon, paystatus, firstname, email, 
amountpaid, SumTotalruns, SumTotalamount
FROM test t1, 
(select t2.regid,
    concat(t2.email, t2.firstname) tempid,  
    COUNT(*) SumTotalruns, 
    SUM(t3.amountpaid) SumTotalamount
    from test t2 left join test t3
    on concat(t2.email , t2.firstname) = concat(t3.email , t3.firstname)
        and t2.paystatus = 'completed'
        and t2.registeredon < t3.registeredon
    group by concat(t2.email, t2.firstname)
    ) tmp
where concat(t1.email , t1.firstname) = tmp.tempid
order by id desc

Output of above query –

regid|   registeredon |   paystatus   |   firstname   |   email   |   amountpaid   |   SumTotalruns   |   SumTotalamount
a1   |   2018-05-05   |   completed   |   ajay        |   a@x.c   |   120          |   15             |   710
z2   |   2018-03-14   |   completed   |   jay         |   j@x.c   |   100          |   2              |   100
z1   |   2018-03-18   |   completed   |   ajay        |   a@x.c   |   100          |   15             |   710
y6   |   2018-02-21   |   pending     |   vijay       |   v@z.c   |   0            |   5              |   150
y2   |   2018-02-23   |   completed   |   vijay       |   v@z.c   |   150          |   5              |   150
y1   |   2018-02-03   |   completed   |   ajay        |   a@x.c   |   150          |   15             |   710
x6   |   2018-01-11   |   pending     |   vijay       |   v@z.c   |   100          |   5              |   150
x5   |   2018-01-01   |   pending     |   ajay        |   a@x.c   |   0            |   15             |   710
x2   |   2018-01-14   |   completed   |   vijay       |   v@z.c   |   100          |   5              |   150
x1   |   2018-01-11   |   completed   |   ajay        |   a@x.c   |   100          |   15             |   710
a5   |   2018-05-03   |   pending     |   ajay        |   a@x.c   |   0            |   15             |   710
a2   |   2018-05-23   |   completed   |   jay         |   j@x.c   |   100          |   2              |   100
z5   |   2018-03-15   |   pending     |   ajay        |   a@x.c   |   0            |   15             |   710

All these update queries will be added to a stored procedure, so if looping is better, that can be used too. Can you please help with finding the most efficient way to get values for these? Will appreciate your help!

Figured the query with the help of another friend

    update test a join
(SELECT
    outers.regid,
    outers.registeredon, outers.paystatus, outers.firstname, outers.email,
    outers.amountpaid,
    sum(InnerAmount) SumTotalAmount,
    count(1) SumTotalRuns
    from test as outers,
        (select id,
        test.regid,concat(test.email, test.firstname) tempid,
        test.amountpaid InnerAmount,
        test.registeredon
        from test
        where test.paystatus = 'completed'
    ) inner_table
    where concat(outers.email,outers.firstname) = inner_table.tempid
    and outers.registeredon >= inner_table.registeredon
    group by regid
) b
on a.regid=b.regid
set a.totalruns = b.SumTotalRuns,
a.totalamount = b.SumTotalAmount

But running this on the main table (total 12.5k rows and 7.6k rows with paystatus='completed') is taking 40+ seconds.
Any help in optimizing this query?

Best Answer

The query offered by Your friend

    update test a join
(SELECT
    outers.regid,
    outers.registeredon, outers.paystatus, outers.firstname, outers.email,
    outers.amountpaid,
    sum(InnerAmount) SumTotalAmount,
    count(1) SumTotalRuns
    from test as outers,
        (select id,
        test.regid,concat(test.email, test.firstname) tempid,
        test.amountpaid InnerAmount,
        test.registeredon
        from test
        where test.paystatus = 'completed'
    ) inner_table
    where concat(outers.email,outers.firstname) = inner_table.tempid
    and outers.registeredon >= inner_table.registeredon
    group by regid
) b
on a.regid=b.regid
set a.totalruns = b.SumTotalRuns,
a.totalamount = b.SumTotalAmount

is close to optimal, needed a lot of minor modifications.

Fields id and regid in inner_table are not used in outer code and must be removed.
Using CONCAT() function prevents using index (if exists) and must be replaced with separate fields in both places it occured.
Fields registeredon, paystatus, firstname, email, amountpaid from outer SELECT statement are not used in UPDATE and must be removed.
To optimize joining process in outer SELECT You may create index by (`firstname`, `email`) or maybe even by (`firstname`, `email`, `registeredon`).

So the result is:

CREATE INDEX idx_firstname_email ON test (firstname, email /* , registeredon */ );

and

UPDATE test a 
JOIN ( SELECT outers.regid,
              SUM(InnerAmount) SumTotalAmount,
              COUNT(1) SumTotalRuns
       FROM test AS outers,
            (SELECT test.email, 
                    test.firstname,
                    test.amountpaid InnerAmount,
                    test.registeredon
             FROM test
             WHERE test.paystatus = 'completed'
            ) inner_table
       WHERE outers.email = inner_table.email 
         AND outers.firstname = inner_table.firstname
         AND outers.registeredon >= inner_table.registeredon
       GROUP BY regid
     ) b ON a.regid=b.regid
SET a.totalruns = b.SumTotalRuns,
    a.totalamount = b.SumTotalAmount;

PROPOSED SOLUTION

SET @ndx = 0;
SET @cur_hash = '';
SELECT entry_id,CONCAT(url_title,IF(ndx>0,CONCAT('-',ndx),'')) url_title
FROM (SELECT
   @new_hash := MD5(url_title),
   (@ndx := IF(@new_hash=@cur_hash,@ndx+1,0)) ndx,
   @cur_hash := MD5(url_title),
   url_title,entry_id
FROM (SELECT url_title,entry_id FROM aaronlax ORDER BY url_title) AA) A;

SAMPLE DATA

use test
DROP TABLE IF EXISTS aaronlax;
CREATE TABLE aaronlax
(
    entry_id int(10) unsigned NOT NULL AUTO_INCREMENT,
    url_title varchar(75) NOT NULL,
    PRIMARY KEY (entry_id),
    KEY (url_title)    
) ENGINE=MyISAM;
INSERt INTO aaronlax (url_title) VALUES
('a-stoney'),('a-stoney'),
('ab-dille'),('ab-dille'),
('ab-dille'),('ac-gifta'),
('ae-jones'),('ae-jones');

SAMPLE DATA LOADED

mysql> use test
Database changed
mysql> DROP TABLE IF EXISTS aaronlax;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE aaronlax
    -> (
    ->     entry_id int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->     url_title varchar(75) NOT NULL,
    ->     PRIMARY KEY (entry_id),
    ->     KEY (url_title)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERt INTO aaronlax (url_title) VALUES
    -> ('a-stoney'),('a-stoney'),
    -> ('ab-dille'),('ab-dille'),
    -> ('ab-dille'),('ac-gifta'),
    -> ('ae-jones'),('ae-jones');
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> select * from aaronlax;
+----------+-----------+
| entry_id | url_title |
+----------+-----------+
|        1 | a-stoney  |
|        2 | a-stoney  |
|        3 | ab-dille  |
|        4 | ab-dille  |
|        5 | ab-dille  |
|        6 | ac-gifta  |
|        7 | ae-jones  |
|        8 | ae-jones  |
+----------+-----------+
8 rows in set (0.00 sec)

mysql>

PROPOSED SOLUTION EXECUTED

mysql> SET @ndx = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @cur_hash = '';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT entry_id,CONCAT(url_title,IF(ndx>0,CONCAT('-',ndx),'')) url_title
    -> FROM (SELECT
    ->    @new_hash := MD5(url_title),
    ->    (@ndx := IF(@new_hash=@cur_hash,@ndx+1,0)) ndx,
    ->    @cur_hash := MD5(url_title),
    ->    url_title,entry_id
    -> FROM (SELECT url_title,entry_id FROM aaronlax ORDER BY url_title) AA) A;
+----------+------------+
| entry_id | url_title  |
+----------+------------+
|        1 | a-stoney   |
|        2 | a-stoney-1 |
|        3 | ab-dille   |
|        4 | ab-dille-1 |
|        5 | ab-dille-2 |
|        6 | ac-gifta   |
|        7 | ae-jones   |
|        8 | ae-jones-1 |
+----------+------------+
8 rows in set (0.00 sec)

mysql>

GIVE IT A TRY !!!

CAVEAT

In case you are wondering why the proposed solution works, let's take a look at the subqueries

INNERMOST SUBQUERY

mysql> SELECT url_title,entry_id FROM aaronlax ORDER BY url_title;
+-----------+----------+
| url_title | entry_id |
+-----------+----------+
| a-stoney  |        1 |
| a-stoney  |        2 |
| ab-dille  |        3 |
| ab-dille  |        4 |
| ab-dille  |        5 |
| ac-gifta  |        6 |
| ae-jones  |        7 |
| ae-jones  |        8 |
+-----------+----------+
8 rows in set (0.00 sec)

mysql>

NEXT LEVEL SUBQUERY

mysql> SELECT
    ->    @new_hash := MD5(url_title),
    ->    (@ndx := IF(@new_hash=@cur_hash,@ndx+1,0)) ndx,
    ->    @cur_hash := MD5(url_title),
    ->    url_title,entry_id
    -> FROM (SELECT url_title,entry_id FROM aaronlax ORDER BY url_title) AA;
+----------------------------------+------+----------------------------------+-----------+----------+
| @new_hash := MD5(url_title)      | ndx  | @cur_hash := MD5(url_title)      | url_title | entry_id |
+----------------------------------+------+----------------------------------+-----------+----------+
| 911adc6db360f218da5069b20cfb1917 |    0 | 911adc6db360f218da5069b20cfb1917 | a-stoney  |        1 |
| 911adc6db360f218da5069b20cfb1917 |    1 | 911adc6db360f218da5069b20cfb1917 | a-stoney  |        2 |
| b468c0dd0ef78fde7cf5ae14765f9a5a |    0 | b468c0dd0ef78fde7cf5ae14765f9a5a | ab-dille  |        3 |
| b468c0dd0ef78fde7cf5ae14765f9a5a |    1 | b468c0dd0ef78fde7cf5ae14765f9a5a | ab-dille  |        4 |
| b468c0dd0ef78fde7cf5ae14765f9a5a |    2 | b468c0dd0ef78fde7cf5ae14765f9a5a | ab-dille  |        5 |
| 3fff0367af8bf5d3b3b0089b8f187c04 |    0 | 3fff0367af8bf5d3b3b0089b8f187c04 | ac-gifta  |        6 |
| 1909ba0f7a57ca081c82d2f7ba54c9e5 |    0 | 1909ba0f7a57ca081c82d2f7ba54c9e5 | ae-jones  |        7 |
| 1909ba0f7a57ca081c82d2f7ba54c9e5 |    1 | 1909ba0f7a57ca081c82d2f7ba54c9e5 | ae-jones  |        8 |
+----------------------------------+------+----------------------------------+-----------+----------+
8 rows in set (0.00 sec)

mysql>

From this level, I only need three columns: entry_id, url_title, ndx in the outer most level

Then, I append to the url_title a hyphen and ndx if ndx is nonzero

MySQL Count rows from another table for each record in table

You are just missing the GROUP BY

YOUR QUERY WITH GROUP BY

SELECT
  student.StudentID,
  student.`Name`,
  COUNT(attendance.AttendanceID) AS Total
FROM
  student
LEFT JOIN attendance ON student.StudentID = attendance.StudentID
GROUP BY student.StudentID,student.`Name`;

SAMPLE DATA

DROP DATABASE IF EXISTS alishaikh; CREATE DATABASE alishaikh;
USE alishaikh
CREATE TABLE student
(
  StudentID CHAR(8) NOT NULL,
  Name VARCHAR(40),
  PRIMARY KEY (StudentID)
);
INSERT INTO student (StudentID,Name) VALUES
('k1052280','Ali Shaikh'),('k1052287','McKenzie Roth'),
('k1052288','Dacey Sullivan'),('k1052294','Zelda Cantu'),
('k1052295','Kimberly Melton'),('k1052296','Tatianna Cantrell'),
('k1052297','Morgan Thornton'),('k1052298','Allistair Barlow'),
('k1052299','Troy Fulton');
CREATE TABLE attendance
(
  AttendanceID INT NOT NULL AUTO_INCREMENT,
  StudentID CHAR(8) NOT NULL,
  PRIMARY KEY (AttendanceID),
  KEY (StudentID)
);
INSERT INTO attendance (StudentID) VALUES
('k1052280'),('k1052280'),('k1052287'),('k1052287'),
('k1052288'),('k1052295'),('k1052295'),('k1052295');

SAMPLE DATA LOADED

mysql> DROP DATABASE IF EXISTS alishaikh; CREATE DATABASE alishaikh;
Query OK, 2 rows affected (0.01 sec)
Query OK, 1 row affected (0.00 sec)
mysql> USE alishaikh
Database changed
mysql> CREATE TABLE student
    -> (
    ->   StudentID CHAR(8) NOT NULL,
    ->   Name VARCHAR(40),
    ->   PRIMARY KEY (StudentID)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO student (StudentID,Name) VALUES
    -> ('k1052280','Ali Shaikh'),('k1052287','McKenzie Roth'),
    -> ('k1052288','Dacey Sullivan'),('k1052294','Zelda Cantu'),
    -> ('k1052295','Kimberly Melton'),('k1052296','Tatianna Cantrell'),
    -> ('k1052297','Morgan Thornton'),('k1052298','Allistair Barlow'),
    -> ('k1052299','Troy Fulton');
Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE attendance
    -> (
    ->   AttendanceID INT NOT NULL AUTO_INCREMENT,
    ->   StudentID CHAR(8) NOT NULL,
    ->   PRIMARY KEY (AttendanceID),
    ->   KEY (StudentID)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO attendance (StudentID) VALUES
    -> ('k1052280'),('k1052280'),('k1052287'),('k1052287'),
    -> ('k1052288'),('k1052295'),('k1052295'),('k1052295');
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

YOUR QUERY WITH GROUP BY EXECUTED

mysql> SELECT
    ->   student.StudentID,
    ->   student.`Name`,
    ->   COUNT(attendance.AttendanceID) AS Total
    -> FROM
    ->   student
    -> LEFT JOIN attendance ON student.StudentID = attendance.StudentID
    -> GROUP BY student.StudentID,student.`Name`;
+-----------+-------------------+-------+
| StudentID | Name              | Total |
+-----------+-------------------+-------+
| k1052280  | Ali Shaikh        |     2 |
| k1052287  | McKenzie Roth     |     2 |
| k1052288  | Dacey Sullivan    |     1 |
| k1052294  | Zelda Cantu       |     0 |
| k1052295  | Kimberly Melton   |     3 |
| k1052296  | Tatianna Cantrell |     0 |
| k1052297  | Morgan Thornton   |     0 |
| k1052298  | Allistair Barlow  |     0 |
| k1052299  | Troy Fulton       |     0 |
+-----------+-------------------+-------+
9 rows in set (0.00 sec)

mysql>

Best Answer

Related Solutions

Mysql – Append duplicate records with count (thesql)

PROPOSED SOLUTION

SAMPLE DATA

SAMPLE DATA LOADED

PROPOSED SOLUTION EXECUTED

GIVE IT A TRY !!!

CAVEAT

INNERMOST SUBQUERY

NEXT LEVEL SUBQUERY

MySQL Count rows from another table for each record in table

YOUR QUERY WITH GROUP BY

SAMPLE DATA

SAMPLE DATA LOADED

YOUR QUERY WITH GROUP BY EXECUTED

GIVE IT A TRY !!!

Related Question