Comparing rows with Mysql query

Question

I have a mysql database, containing a table with following columns: (Day_T1, Day_T2, Day_T3, Day_T4) and a unique ID for each row. they are all integers. I'd like to compare each row with the previous rows in order. And the comparison criteria I need is to count the values that are +1 of the previous row.

So if we are comparing 16 and 15, since it's up +1, it should be counted as 1. and I need the total of counts for each row comparison.

The rows may have repeating values. For example, if you take the first two rows in the below table, (19,18,15,16) and (20,17,15,15), the second row has two 15s. So each should be taken once for counting. Here the count should be 2.

A sample table would look like this:

ID  Day_T1  Day_T2  Day_T3  Day_T4

70  19      18      15      16
69  20      17      15      15
68  24      25      16      17
67  19      18      14      12
66  19      17      14      13
65  17      16      15      14

And the expected results would be something like this:

ID  69  68  67  66  65  64  etc

70  2   0   1   2   3   etc             
69      1   2   2   2   etc             
68          0   0   2   etc

I have only basic knowledge of mysql queries. I'm not entirely sure mysql is the right way to do it. If it's not, and I'd be glad if you can guide me in the right direction.

Thanks in advance.

SOLUTION acc. to Cobra_Fast's answer:

SELECT
    d1.id,
    d2.id,
    IF((d1.day1-d2.day1)=1,1,0) +
    IF((d1.day1-d2.day2)=1,1,0) +
    IF((d1.day1-d2.day3)=1,1,0) +
    IF((d1.day1-d2.day4)=1,1,0) +
    IF((d1.day2-d2.day2)=1,1,0) +
    IF((d1.day2-d2.day1)=1,1,0) +
    IF((d1.day2-d2.day3)=1,1,0) +
    IF((d1.day2-d2.day4)=1,1,0) +
    IF((d1.day3-d2.day3)=1,1,0) +
    IF((d1.day3-d2.day1)=1,1,0) +
    IF((d1.day3-d2.day2)=1,1,0) +
    IF((d1.day3-d2.day4)=1,1,0) +
    IF((d1.day4-d2.day4)=1,1,0) +
    IF((d1.day4-d2.day1)=1,1,0) +
    IF((d1.day4-d2.day2)=1,1,0) +
    IF((d1.day4-d2.day3)=1,1,0)     AS onecount
FROM days AS d1 , days AS d2
WHERE d1.id IS NOT NULL AND d2.id IS NOT NULL AND d1.id<>d2.id;

where table structure is like:

CREATE TABLE days (
    id INT AUTO_INCREMENT,
    day1 INT,
    day2 INT,
    day3 INT,
    day4 INT,
    PRIMARY KEY (id)
);

INSERT INTO days VALUES
    (70, 19, 18, 15, 16),
    (69, 20, 17, 15, 15),
    (68, 24, 25, 16, 17),
    (67, 19, 18, 14, 12),   
    (66, 19, 17, 14, 13),    
    (65, 17, 16, 15, 14);

65 to 70 counts 2?

Sal
– Sal

2019-01-16 15:09:52 +00:00
Commented Jan 16, 2019 at 15:09 — Sal
– Sal, Commented Jan 16, 2019 at 15:09
sorry, my mistake. I corrected it.

user223549
– user223549

2019-01-16 15:11:23 +00:00
Commented Jan 16, 2019 at 15:11 — user223549
– user223549, Commented Jan 16, 2019 at 15:11

Cobra_Fast · Accepted Answer · 2019-01-16 13:46:27Z

1

You could do something like this:

SELECT
    d1.id,
    d2.id,
    IF(ABS(d1.day1-d2.day1)=1,1,0) +
    IF(ABS(d1.day2-d2.day2)=1,1,0) +
    IF(ABS(d1.day3-d2.day3)=1,1,0) +
    IF(ABS(d1.day4-d2.day4)=1,1,0) AS onecount
FROM days AS d1
LEFT JOIN days AS d2 ON (d1.id = (d2.id + 1))
WHERE d1.id IS NOT NULL AND d2.id IS NOT NULL;

Result looks like this:

id  id  onecount
66  65  3
67  66  2
68  67  0
...

Fiddle: https://www.db-fiddle.com/f/7VAG1GTgj6CXiLKkPAHJtf/1

answered Jan 16, 2019 at 13:46

Cobra_Fast

16.2k8 gold badges65 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user223549 Over a year ago

thank you for your answer. How'd I do it work for every one of previous rows? e.g for the ID=70 row, it should be compared with 69,68,67,etc in order. your solution only compares with the previous row alone.

Cobra_Fast Over a year ago

To compare everything against everything simply remove the LEFT JOIN line and add , days AS d2 to the FROM line, so it reads FROM days AS d1, days AS d2.

user223549 Over a year ago

I checked the results. I think it counts +1s both ways. so from id 69 to id 70, it should be 2. in the results it is 3. and the others seem to be the same.

Cobra_Fast Over a year ago

Well then using ABS() might not be correct. I'm sure you can figure it out from here.

user223549 Over a year ago

yes, I removed ABS() already, but unfortunately the results are still not correct.

Collectives™ on Stack Overflow

Comparing rows with Mysql query

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related