patternsqlMinor
Subtract values in same column from 2 different dates in same Mysql table
Viewed 0 times
samecolumnsubtractdatesdifferentmysqlvaluesfromtable
Problem
I have a MySql table like:
1) What is the simplest query for calculating and returning the difference in
For example:
2) Also there will be some cases when there will be no records for some of the dates for an
3) What should be the correct indices to optimize the speed of the query?
I did come across this query that might work but it is a huge query and I am sure that there can be a more compact and elegant solution :)
Item Date Likes
Book 2012-05-20 402
Book 2012-05-21 432
Book 2012-05-22 450
Pen 2012-05-20 20
Pen 2012-05-21 20
Pen 2012-05-22 251) What is the simplest query for calculating and returning the difference in
Likes column between 2 set dates for all of the Item values, ordered by the Difference?For example:
Likes difference between 2012-05-20 and 2012-05-21 returns next results:Item Difference
Book 30
Pen 0Likes difference between 2012-05-20 and 2012-05-22 returns next results:Item Difference
Book 48
Pen 52) Also there will be some cases when there will be no records for some of the dates for an
Item in the table, in those cases no results for that Item should be returned.3) What should be the correct indices to optimize the speed of the query?
I did come across this query that might work but it is a huge query and I am sure that there can be a more compact and elegant solution :)
Solution
SELECT t1.item, t2.likes - t1.likes difference
FROM table t1, table t2
WHERE t1.item = t2.item
AND t1.date = @first_date
AND t2.date = @second_dateIf you need all items (ever the data on @first_date or @second_date is not present), rewrite from cartesian to FULL JOIN and use COALESCE to replace NULLs with zeros.
Code Snippets
SELECT t1.item, t2.likes - t1.likes difference
FROM table t1, table t2
WHERE t1.item = t2.item
AND t1.date = @first_date
AND t2.date = @second_dateContext
StackExchange Database Administrators Q#232498, answer score: 3
Revisions (0)
No revisions yet.