Trouble with mysql subquery-CodePudding

I have a table with the following values: Name, Street,I'd , Value, Date.

I need to combine Name, Street, Id and make 2 subgroups by date. I want to compare the value in row with the same name, street and id but different date. And write only the ones with different value

Example:

Mike, Street 1 , idtag , 5 , 11.5.2022

Mike, street 1 , idtag , 10 , 10.5.2022

I want to write the difference in value with the name, street, id combination.

All the solutions I have tried take way to long

CodePudding user response：

dYou could use an aggregation approach here. Assuming that you want to flag any name, street, and ID combination which have 2 or more records on different dates, you may try:

SELECT name, street, ID, MAX(val) - MIN(val) AS diff
FROM yourTable
GROUP BY name, street, ID
HAVING MIN(date) <> MAX(date);

To use this logic for a specific pair of records, whose (unique) date values are known, use this version:

SELECT name, street, ID, MAX(val) - MIN(val) AS diff
FROM yourTable
GROUP BY name, street, ID
HAVING MIN(date) = '2022-05-10' AND MAX(date) = '2022-05-11';