Find the difference from prior record sorted by field

Question

I'm using MySQL 5.6.

This is my source table:

form_unique_identifier  field_number    field_label         total_drop_off      total_visitors
FA11567953              0               Name                4                   100     
FA11567953              1               Dropdown            5                   100     
FA11567953              3               File                32                  100     
FA11567953              4               Multi select field  10                  100     

FA45345345              0               Name                1                   233     
FA45345345              11              Dropdown            7                   233             
FA45345345              31              File                2                   233         
FA45345345              44              Multi select field  3                   233     

FA45345356              2               Name                5                   77          
FA45345356              4               Dropdown            1                   77          
FA45345356              6               File                7                   77      
FA45345356              8               Multi select field  6                   77

I am trying to find the total_field_visitors.

The formula is -

total_field_visitors = total_visitors - total_drop_off of prior record

And I need it based on form_unique_identifier and sorted by field_number.

I wrote this query, but it doesn't seem to work well:

UPDATE table1 a
inner join table1 b
on a.form_unique_identifier = b.form_unique_identifier AND a.field_number < MIN(b.field_number)
SET a.total_field_visitors = a.total_visitors - b.total_drop_off

If it is the first field, then the total_field_visitors should be set equal to total_visitors.

This is my output table:

form_unique_identifier  field_number    field_label         total_drop_off      total_visitors   total_field_visitors
FA11567953              0               Name                4                   100              100
FA11567953              1               Dropdown            5                   100              96
FA11567953              3               File                32                  100              91
FA11567953              4               Multi select field  10                  100              59

FA45345345              0               Name                1                   233              233
FA45345345              11              Dropdown            7                   233              232
FA45345345              31              File                2                   233              225
FA45345345              44              Multi select field  3                   233              223

FA45345356              2               Name                5                   77               77
FA45345356              4               Dropdown            1                   77               72
FA45345356              6               File                7                   77               71
FA45345356              8               Multi select field  6                   77               64

D-Shih · Accepted Answer · 2018-09-15T15:10:38.580

You are looking for SUM with window function, but It only supports mysql version higher than 8.0.

There is another way can make it.

using a subquery to select to do SUM with window function.

Schema (MySQL v5.6)

CREATE TABLE table1 (
    form_unique_identifier varchar(50),
    field_number int,
    field_label varchar(50),
    total_drop_off int,
    total_visitors int  
);



INSERT INTO table1 VALUES ('FA11567953',0,'Name',4  ,100);    
INSERT INTO table1 VALUES ('FA11567953',1,'Dropdown',5  ,100);    
INSERT INTO table1 VALUES ('FA11567953',3,'File',32 ,100);    
INSERT INTO table1 VALUES ('FA11567953',4,'Multi select field',10 ,100);    
INSERT INTO table1 VALUES ('FA45345345',0 ,'Name',1,233);     
INSERT INTO table1 VALUES ('FA45345345',11,'Dropdown',7,233);             
INSERT INTO table1 VALUES ('FA45345345',31,'File',2,233);         
INSERT INTO table1 VALUES ('FA45345345',44,'Multi select field',3,233);     
INSERT INTO table1 VALUES ('FA45345356',2 ,'Name',5,77);          
INSERT INTO table1 VALUES ('FA45345356',4 ,'Dropdown',1,77);          
INSERT INTO table1 VALUES ('FA45345356',6 ,'File',7,77);      
INSERT INTO table1 VALUES ('FA45345356',8 ,'Multi select field',6,77);

Query #1

SELECT 
  form_unique_identifier,
  field_number,
  field_label,
  total_drop_off,
  total_visitors,
  (total_visitors - prevVal) total_field_visitors 
FROM (
    SELECT t1.*,coalesce((
                 SELECT sum(total_drop_off) 
                 FROM table1 tt
                 WHERE tt.form_unique_identifier = t1.form_unique_identifier
                 and t1.field_number > tt.field_number    
                 order by tt.field_number DESC
            ),0)prevVal
    FROM table1 t1
) t1;

| form_unique_identifier | field_number | field_label        | total_drop_off | total_visitors | total_field_visitors |
| ---------------------- | ------------ | ------------------ | -------------- | -------------- | -------------------- |
| FA11567953             | 0            | Name               | 4              | 100            | 100                  |
| FA11567953             | 1            | Dropdown           | 5              | 100            | 96                   |
| FA11567953             | 3            | File               | 32             | 100            | 91                   |
| FA11567953             | 4            | Multi select field | 10             | 100            | 59                   |
| FA45345345             | 0            | Name               | 1              | 233            | 233                  |
| FA45345345             | 11           | Dropdown           | 7              | 233            | 232                  |
| FA45345345             | 31           | File               | 2              | 233            | 225                  |
| FA45345345             | 44           | Multi select field | 3              | 233            | 223                  |
| FA45345356             | 2            | Name               | 5              | 77             | 77                   |
| FA45345356             | 4            | Dropdown           | 1              | 77             | 72                   |
| FA45345356             | 6            | File               | 7              | 77             | 71                   |
| FA45345356             | 8            | Multi select field | 6              | 77             | 64                   |

View on DB Fiddle

If you want to do UPDATE just use UPDATE ... JOIN

Schema (MySQL v5.6)

CREATE TABLE table1 (
    form_unique_identifier varchar(50),
    field_number int,
    field_label varchar(50),
    total_drop_off int,
    total_visitors int,
    total_field_visitors int
);



INSERT INTO table1 VALUES ('FA11567953',0,'Name',4  ,100,0);     
INSERT INTO table1 VALUES ('FA11567953',1,'Dropdown',5  ,100,0);     
INSERT INTO table1 VALUES ('FA11567953',3,'File',32 ,100,0);     
INSERT INTO table1 VALUES ('FA11567953',4,'Multi select field',10 ,100,0);     
INSERT INTO table1 VALUES ('FA45345345',0 ,'Name',1,233,0);      
INSERT INTO table1 VALUES ('FA45345345',11,'Dropdown',7,233,0);              
INSERT INTO table1 VALUES ('FA45345345',31,'File',2,233,0);          
INSERT INTO table1 VALUES ('FA45345345',44,'Multi select field',3,233,0);      
INSERT INTO table1 VALUES ('FA45345356',2 ,'Name',5,77,0);           
INSERT INTO table1 VALUES ('FA45345356',4 ,'Dropdown',1,77,0);           
INSERT INTO table1 VALUES ('FA45345356',6 ,'File',7,77,0);       
INSERT INTO table1 VALUES ('FA45345356',8 ,'Multi select field',6,77,0);


UPDATE table1 a 
   JOIN (
     SELECT t1.*,coalesce((
                 SELECT sum(total_drop_off) 
                 FROM table1 tt
                 WHERE tt.form_unique_identifier = t1.form_unique_identifier
                 and t1.field_number > tt.field_number    
                 order by tt.field_number DESC
            ),0)prevVal
    FROM table1 t1
   ) b ON a.form_unique_identifier = b.form_unique_identifier
   AND a.field_number = b.field_number 
   AND a.field_label = b.field_label
   AND a.total_drop_off = b.total_drop_off
   SET a.total_field_visitors = b.total_visitors - b.prevVal

View on DB Fiddle

score 1 · Answer 2 · answered Sep 16 '18 at 15:49

You can simply use a correlated query in the SELECT clause:

SELECT t.*, total_visitors - (
    SELECT COALESCE(SUM(total_drop_off), 0)
    FROM yourdata AS x
    WHERE x.form_unique_identifier = t.form_unique_identifier AND x.field_number < t.field_number
) AS total_field_visitors
FROM yourdata AS t

You can use the above for UPDATE with subtle tweaks:

UPDATE yourdata AS toupdate
INNER JOIN (
    SELECT form_unique_identifier, field_number, total_visitors - (
        SELECT COALESCE(SUM(total_drop_off), 0)
        FROM yourdata AS x
        WHERE x.form_unique_identifier = t.form_unique_identifier AND x.field_number < t.field_number
    ) AS total_field_visitors
    FROM yourdata AS t
) AS calcdata ON toupdate.form_unique_identifier = calcdata.form_unique_identifier AND toupdate.field_number = calcdata.field_number
SET toupdate.total_field_visitors = calcdata.total_field_visitors

score 0 · Answer 3 · answered Sep 15 '18 at 19:49

Your question is very similar to another that relies on a "running total" context and does not use complex features. It works completely with the use of MySQL @variables...

select
      t1.form_unique_identifier,
      t1.Total_Visitors,
      t1.total_drop_off,
      t1.total_Visitors - if( @lastFUI = t1.form_unique_identifier, @totDropOff, 0 ) as Remaining,
      @totDropOff := if( @lastFUI = t1.form_unique_identifier, @totDropOff + t1.total_drop_off, t1.total_drop_off ) as newDropOffTotal,
      @lastFUI := t1.form_unique_identifier
   from
      tab1 t1,
      ( select @lastFUI := '',
               @totDropOff := 0) SQLVars
   order by
      t1.form_unique_identifier,
      t1.field_number

SQL-Fiddle answer

The preparation is done with declaring MySQL variables via a query just to default them to empty string and zero. The alias SQLVars is not joined as it only returns one row anyhow and would never cause a Cartesian result. Now we have values set... the last Form Unique Identifier (@lastFUI), and the running total dropped off cumulative per each form.

I am including extra columns to show the progression of the values as each row is processed. You can obviously remove or add more as you need.

The line "t1.total_Visitors - if()" is basically stating if the record I am on is the SAME as the last form ID, then subtract how many were dropped at the last row from the total. If it is a different form, don't subtract anything.

Next, the line @totDropOff := is basically checking... If the last form I was on is the same as the form unique ID I am on NOW, I want to set the total cumulative drop-off count equal to what it was from that last record PLUS the total being dropped off now. If the form unique ID is different, just set the @totDropOff = the drop off count on this new record as the basis to show on its subsequent form record.

Only after the cumulative total has been properly set do we want to preserve the current record's Form Unique ID into the variable for next comparison.

The ORDER BY Clause is critical to make sure the data is processed in order by the unique ID and the field number as you indicated in your requirement.

For similar and explanation of another (in case you ever need a slightly different approach), check out this other MySQL post

score -1 · Answer 4 · edited Jan 31 '20 at 03:59

-1

Firstly you can do the cumulative sum excluding the first row with PRECEDING clause then subtract that from the total_visitor column. You can try below query -

SELECT *, total_visitors - IFNULL(sum(total_drop_off)
     over(partition by (form_unique_identifier) order by field_number
               ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),0) totals
FROM tab1
GROUP BY form_unique_identifier, field_number, field_label,
         total_drop_off, total_visitors
ORDER BY form_unique_identifier;

Here is the db_fiddle - https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=b8bd73ce6d971c590ba747c181de8469

edited Jan 31 '20 at 03:59

Rick James

135,179
13
127
222

answered Sep 13 '18 at 14:51

Ankit Bajpai

13,128
4
25
40

I just overlooked the requirements. It will not work on MySQL 5.6. Window functions introduced in MySQL only from version 8.0. – Ankit Bajpai Sep 13 '18 at 18:10
Can you please share the query for 5.6? – dang Sep 13 '18 at 18:25

Find the difference from prior record sorted by field

4 Answers4