Skieth Skieth - 4 months ago 20
MySQL Question

SQL: Month over Month Query

Hi I have a simple query for my database that show sales per transaction base on custom date that I will set using this query below.

Example i select date and shows my report base on date i set.

Existing Query

$strSQL = "SELECT IFNULL(`ProductName`, 'Total') AS 'ProductName',`
Closed On`,
SUM(`Quantity`) as Quantity ,
SUM(`Price Paid`) as Price
FROM `".$user."_products`
WHERE DATE( `Closed On`) >= '$datefrom'
AND DATE(`Closed On`) <='$dateto'
GROUP by `ProductName` with ROLLUP";


Existing Output

---------------------------------------
|Product Name |Quantity |Total Sales |
---------------------------------------
|Shoes | 12 | 1000 |
|Tshirt | 232 | 2312 |
|Total | 352 | 3312 |
---------------------------------------


But I want to query my table by month over month example output is(jan-dec)

--------------------------------------------
|Product Name |Jan |Feb | March| Total |
--------------------------------------------
|Shoes | 500 | 1000 | 215 | 121542 |
|Tshirt | 232 | 2312 | 1235 | 123123 |
--------------------------------------------


Thanks!

Answer

You can do this with a conditional sum. For each month you check if the row is for that month and if so use the value you want to sum, if not you just use 0.

$strSQL =  "SELECT  IFNULL(`ProductName`, 'Total') AS 'ProductName',`
                    Closed On`, 
                    SUM(IF(MONTH(`Closed On`) = 1, `Quantity`, 0)) AS `Jan`,
                    SUM(IF(MONTH(`Closed On`) = 2, `Quantity`, 0)) AS `Feb`,
                    SUM(IF(MONTH(`Closed On`) = 3, `Quantity`, 0)) AS `Mar`,
                    SUM(IF(MONTH(`Closed On`) = 4, `Quantity`, 0)) AS `Apr`,
                    SUM(IF(MONTH(`Closed On`) = 5, `Quantity`, 0)) AS `May`,
                    SUM(IF(MONTH(`Closed On`) = 6, `Quantity`, 0)) AS `Jun`,
                    SUM(IF(MONTH(`Closed On`) = 7, `Quantity`, 0)) AS `Jul`,
                    SUM(IF(MONTH(`Closed On`) = 8, `Quantity`, 0)) AS `Aug`,
                    SUM(IF(MONTH(`Closed On`) = 9, `Quantity`, 0)) AS `Sep`,
                    SUM(IF(MONTH(`Closed On`) = 10, `Quantity`, 0)) AS `Oct`,
                    SUM(IF(MONTH(`Closed On`) = 11, `Quantity`, 0)) AS `Nov`,
                    SUM(IF(MONTH(`Closed On`) = 12, `Quantity`, 0)) AS `Dec`,
                    SUM(`Quantity`) as Quantity ,
                    SUM(`Price Paid`) as Price 
FROM `".$user."_products` 
WHERE DATE( `Closed On`) >= '$datefrom' 
AND DATE(`Closed On`) <='$dateto' 
GROUP by `ProductName` with ROLLUP";

EDIT

Doing by year would be more tricky. As a 1 off it is pretty much the same as the above, just checking the year rather than the month. However if this is for an online report then you probably want the window of years to move (ie, you might be interested in 2011 to 2016 this year, but in 2020 that has probably moved to 2015 to 2020). In which case you probably need to dynamically build the query in php (or even in a MySQL stored procedure). There are ways around this but they will still be messy (you can't really change the returned column names in a query, rather you need to change the query in advance to give the returned column names you want).

Basic query:-

$strSQL =  "SELECT  IFNULL(`ProductName`, 'Total') AS 'ProductName',
                    `Closed On`, 
                    SUM(IF(YEAR(`Price Paid`) = 2006, `Quantity`, 0)) AS `2006`,
                    SUM(IF(YEAR(`Price Paid`) = 2007, `Quantity`, 0)) AS `2007`,
                    SUM(IF(YEAR(`Price Paid`) = 2008, `Quantity`, 0)) AS `2008`,
                    SUM(IF(YEAR(`Price Paid`) = 2009, `Quantity`, 0)) AS `2009`,
                    SUM(IF(YEAR(`Price Paid`) = 2010, `Quantity`, 0)) AS `2010`,
                    SUM(IF(YEAR(`Price Paid`) = 2011, `Quantity`, 0)) AS `2011`,
                    SUM(IF(YEAR(`Price Paid`) = 2012, `Quantity`, 0)) AS `2012`,
                    SUM(IF(YEAR(`Price Paid`) = 2013, `Quantity`, 0)) AS `2013`,
                    SUM(IF(YEAR(`Price Paid`) = 2014, `Quantity`, 0)) AS `2014`,
                    SUM(IF(YEAR(`Price Paid`) = 2015, `Quantity`, 0)) AS `2015`,
                    SUM(IF(YEAR(`Price Paid`) = 2016, `Quantity`, 0)) AS `2016`,
                    SUM(IF(YEAR(`Price Paid`) = 2017, `Quantity`, 0)) AS `2017`,
                    SUM(`Quantity`) as Quantity ,
                    SUM(`Price Paid`) as Price 
FROM `".$user."_products` 
WHERE DATE( `Closed On`) >= '$datefrom' 
AND DATE(`Closed On`) <='$dateto' 
GROUP by `ProductName` with ROLLUP";