sohal07 sohal07 - 2 months ago 5
SQL Question

Need help to make sql query to compare data column-wise

I have a mysql table which collects daily sales data (date, sale, gst etc). I need a select statement to compare daily sales for any given date with same date last year and year before, to see the how much we made on that day in previous years.

To give an idea of how the output table will look, I have an example table in jsfiddle.

And here is the sql table data in sqlfiddle.

I tried this statement:

SELECT * FROM sales_chc WHERE MONTH(sale_date) = '1'


It of course gives me the filtered results that I want but I couldn't figure out how to display the results in the give table example.

Answer

By changing year and month in the WHERE statement, you can get a report for whatever month/year combination you need.

SELECT sn.sale_date AS SaleY0 , sn.EFTPOS AS EFTPOSY0, sn.total AS totalY0, 
      s1.sale_date AS SaleY1 , s1.EFTPOS AS EFTPOSY1, s1.total AS totalY1,
      s2.sale_date AS SaleY2 , s2.EFTPOS AS EFTPOSY2, s2.total AS totalY2
FROM sales_chc sn
LEFT JOIN sales_chc S1
ON s1.sale_date=(sn.sale_date -INTERVAL 1 YEAR)
LEFT JOIN sales_chc S2
ON s2.sale_date=(sn.sale_date -INTERVAL 2 YEAR)
WHERE YEAR(SN.sale_date)=2016 AND MONTH(SN.sale_date)=1

SQL fiddle

Comments