CODEWITHSUNDEEP
mysqlsql
J.J
J.J

Reputation: 63

Sum of repeating values in successive rows

I have a table like this:

id col1 col2 col3
10  1          3
9   1    2     3
8        2     3
7        2     3
6   1    2    
5              3

Each column has one value only or null. Eg. Col1 has 1 or empty. Col2 has 2 or empty.

I'd like to get the sum of repeating values only between two successive rows. so the result would look like this:

I need to get the sum of total repeating values in each row. 

id col1 col2 col3   Count
10  1          3     2    (shows the repeating values between id10 & id9 rows)
9   1    2     3     2    (shows the repeating values between id9 & id8 rows)
8        2     3     1
7        2           1
6   1    2           0
5              3

I googled and tried some queries I found on the web but couldn't get the right result. Thanks in advance for your help.

To further clarify, for example: id10 row has (1,,3) and id9 row has (1,2,3). so there is two values repeating. so count is 2.

Upvotes: 2

Views: 224

Answers (4)

nice_dev
nice_dev

Reputation: 17805

If there are gaps between id values for the next row, you could have user defined variables to explicitly assign values to rows in their natural ordering in the table. Rest logic remains the same as already answered. You would do an inner join between current row number and next row number to get the col1,col2 and col3 values and use coalesce for computation of count.

select derived_1.*,
  coalesce((derived_1.col1 = derived_2.col1), 0) + 
  coalesce((derived_1.col2 = derived_2.col2), 0) + 
  coalesce((derived_1.col3 = derived_2.col3), 0) count
from (
        select @row := @row + 1 as row_number,t1.*
        from tablename t1,(select @row := 0) d1
      ) derived_1
left join (
       select *
       from (
             select @row2 := @row2 + 1 as row_number,t2.*
             from tablename t2,(select @row2 := 0) d2
            ) d3
        ) derived_2
on derived_1.row_number + 1 = derived_2.row_number;

Demo: https://www.db-fiddle.com/f/wAzb67zSEfbZKg5RywQvC8/1

Upvotes: 1

Strawberry
Strawberry

Reputation: 33935

And if there are gaps...

SELECT a.id 
     , a.col1 
     , a.col2 
     , a.col3 
     , COALESCE(a.col1 = b.col1,0) + COALESCE(a.col2 = b.col2,0) + COALESCE(a.col3 = b.col3,0) n 
  FROM 
     ( SELECT x.*
            , MIN(y.id) y_id 
         FROM my_table x 
         JOIN my_table y 
           ON y.id > x.id 
        GROUP 
           BY x.id
      ) a 
   LEFT
   JOIN my_table b 
     ON b.id = a.y_id;

Were you to restructure your schema, then you could do something like this instead...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id INT NOT NULL 
,val INT NOT NULL
,PRIMARY KEY(id,val)
);

INSERT INTO my_table VALUES
(10,1),
(10,3),
( 9,1),
( 9,2),
( 9,3),
( 8,2),
( 8,3),
( 7,2),
( 7,3),
( 6,1),
( 6,2),
( 5,3); 

SELECT a.id
     , COUNT(b.id) total 
  FROM 
     ( SELECT x.*
            , MIN(y.id) next 
         FROM my_table x 
         JOIN my_table y 
           ON y.id > x.id 
        GROUP 
           BY x.id
            , x.val
     ) a 
  LEFT 
  JOIN my_table b 
    ON b.id = a.next 
   AND b.val = a.val 
 GROUP 
    BY a.id;
+----+-------+
| id | total |
+----+-------+
|  5 |     0 |
|  6 |     1 |
|  7 |     2 |
|  8 |     2 |
|  9 |     2 |
+----+-------+

Upvotes: 2

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

You can use :

select t1_ID, t1_col1,t1_col2,t1_col3, count
  from
(
select t1.id as t1_ID, t1.col1 as t1_col1,t1.col2 as t1_col2,t1.col3 as t1_col3, t2.*,
       case when t1.col1 = t2.col1 then 1 else 0 end +
       case when t1.col2 = t2.col2 then 1 else 0 end +
       case when t1.col3 = t2.col3 then 1 else 0 end as count
  from tab t1
  left join tab t2 
    on t1.id = t2.id + 1
 order by t1.id
) t3
order by t1_ID desc;

Demo

Upvotes: 1

forpas
forpas

Reputation: 164089

If the ids are consecutive and there are no gaps, you can do it with a self join:

select 
  t.*,
  coalesce((t.col1 = tt.col1), 0) + 
  coalesce((t.col2 = tt.col2), 0) + 
  coalesce((t.col3 = tt.col3), 0) count
from tablename t left join tablename tt
on tt.id = t.id - 1

See the demo.
Results:

| id  | col1 | col2 | col3 | count |
| --- | ---- | ---- | ---- | ----- |
| 10  | 1    |      | 3    | 2     |
| 9   | 1    | 2    | 3    | 2     |
| 8   |      | 2    | 3    | 1     |
| 7   |      | 2    |      | 1     |
| 6   | 1    | 2    |      | 0     |
| 5   |      |      | 3    | 0     |

Upvotes: 2

Related Questions