How can I get the difference between the individual maximum values of different days?
I am new in MySQL, I am trying to find:
The difference between a given day's maximum value occurred and the previous day's maximum value.
I was able to get the maximum values for dates via:
select max(`bundle_count`), `Production_date` from `table` group by `Production_date`
But I don't know how to use SQL to calculate the differences between maximums for two given dates.
am expecting output like this
Please help me.
Update 1: Here is a fiddle, http://sqlfiddle.com/#!2/818ad/2, that I used for testing.
Update 2: Here is a fiddle, http://sqlfiddle.com/#!2/3f78d/10 that I used for further refining/fixing, based on Sandy's comments.
Update 3: For some reason the case where there is no previous day was not being dealt with correctly. I thought it was. However, I've updated to make sure that works (a bit cumbersome--but it appears to be right. Last fiddle: http://sqlfiddle.com/#!2/3f78d/45
I think @Grijesh conceptually got you the main thing you needed via the self-join of the input data (so make sure you vote up his answer!). I've cleaned up his query a bit on syntax (building off of his query!):
SELECT DATE(t1.`Production_date`) as theDate, MAX( t1.`bundle_count` ) AS 'max(bundle_count)', MAX( t1.`bundle_count` ) - IF( EXISTS ( SELECT date(t2.production_date) FROM input_example t2 WHERE t2.machine_no = 1 AND date_sub(date(t1.production_date), interval 1 day) = date(t2.production_date) ), ( SELECT MAX(t3.bundle_count) FROM input_example t3 WHERE t3.machine_no = 1 AND date_sub(date(t1.production_date), interval 1 day) = date(t3.production_date) GROUP BY DATE(t3.production_date) ), 0 ) AS Total_Bundles_Used FROM `input_example` t1 WHERE t1.machine_no = 1 GROUP BY DATE( t1.`production_date` )
Note 1: I think @Grijesh and I were cleaning up the query syntax issues at the same time. It's encouraging that we ended up with very similar versions after we were both doing cleanup. My version differs in using
IFNULL() for when there is no preceding data. I also ended up with a
DATE_SUB, and I made sure to reduce various dates to mere dates without time component, via
Note 2: I originally had not fully understood your source tables, so I thought I needed to implement a running count in the query. But upon better inspection, it's clear that your source data already has a running count, so I took that stuff back out.
- A Linux, Apache, MySQL, PHP (LAMP) stack is a combination of open source software that is typically installed together to enable a server to host dynamic […]
- MySQL is an the most popular open-source database management system, in most of hosting company its has been bundle by LAMP(Linux, Apache, MySQL, PHP/Python/Perl) stack. Another alternative […]
- MySQL is the world’s most popular open-source database. Despite its powerful features, MySQL is simple to set up and easy to use. Below are some instructions […]