sohal07 sohal07 - 1 year ago 63
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 Source

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, AS totalY0, 
      s1.sale_date AS SaleY1 , s1.EFTPOS AS EFTPOSY1, AS totalY1,
      s2.sale_date AS SaleY2 , s2.EFTPOS AS EFTPOSY2, 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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download