How can I get the difference between the individual maximum values of different days?


Problem :

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.

Please find the input available

am expecting output like this Expecting Output

Please help me.



Solution :

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 DATE()

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.


Mysql Tutorials

Mysql Howto..

How to add up values in the table row which is retrieved from MySQL?

In MySQL, how do I insert only when row doesn't exist and update only when existing version is less

How do I connect a desktop app to an hosted database

MySQL How to GROUP_CONCAT a specific field?

In BASH, How do i connect to local system DB2 or MySQL to do a simple query?

How to send query from iOS to PHP code

How insert 2 related records in mysql

How do I add items to a list box using MySQL

in MySQL, how to use information from one table to update another table

How to add ' as a value into SQL

mysql how to shorten count 5 results

How to update timestamp in a mysql database?

How To Echo Values Stored As Array in MYSQL?

How to correctly sum a 2 column query in MySQL

How to perform this type of MySQL query - selecting data based on 3 tables?

How to query MYSQL when clicked?

How to join multiple occurrences of record against master record with filter

How to use vagrant in development and how to use shared folders?

How to check if record deleted was last record left MySQL PHP

How to retrieve imageid and insert it into another table? (Mysqli)

How to split comma separated string of records and arrange then sequentially in MySQL?

How to print a MySQL query results as XML in PHP?

How can I simultaneously query all blog options table in a Wordpress multisite installation (3.0)?

How to delete sleep process in Mysql

How to sync SQLite database on Android phone with MySQL database on server?

How to change value for innodb_buffer_pool_size in MySQL on Mac OS?

How do I use SESSION data to SELECT 1 database entry and then INSERT into another table with data from a form as well with PHP and MySQL?

MySQL database issue with international users hitting a US server. How is this normally handled?

How to pass data dynamically to mysql query

MySQL error 1093. how to delete from this