CODEWITHSUNDEEP
mysql
user223549
user223549

Reputation: 117

Comparing rows with Mysql query

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);

Upvotes: 3

Views: 82

Answers (1)

Cobra_Fast
Cobra_Fast

Reputation: 16061

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

Upvotes: 1

Related Questions