Sangeeth Dev Sangeeth Dev - 5 months ago 10
PHP Question

Select totals from database table based on month

I have a table which has product quantity column and the database has multiple entries in a single month.

Date format is (YYYY-MM-DD)

Date Quantity
2016-03-01 1200
2016-03-05 200
2016-04-05 500
2016-04-10 1000
2016-05-05 850
2016-05-10 50


So I want data as:


  • March (2016-03-01 to 2016-03-31) = 1400

  • April (2016-04-01 to 2016-04-30) = 1500

  • May (2016-05-01 to 2016-05-31) = 900



How can I do this?

Answer

Use the following query and it will return the result that you want.

' date field ' is the name of the column where date is inserted and Tablename is the name of the Table.

   SELECT   MONTH(date field)  as  month ,  YEAR(date field) as year , SUM(quantity) as 
   quantity  FROM  Tablename  GROUP  BY  MONTH( date field )