SQL query to retrieve SUM in various DATE ranges

Question

I have a table with information about sold products, the customer, the date of the purchase and summary of sold units.

The result I am trying to get should be 4 rows where the 1st three are for January, February and March. The last row is for the products that weren't sold in these 3 months.

Here is the table. http://imageshack.us/a/img823/8731/fmlxv.jpg

The table columns are:

id
sale_id
product_id
quantity
customer_id
payment_method_id
total_price
date
time

So in the result the 1st 3 row would be just:

• January, SUM for January
• February, SUM for February
• March, SUM for MArch and the next row should be for April, but there are no items in April yet, so I don't really know how to go about all this.

Editor's note: based on the linked image, the columns above would be for the year 2013.

Solution 1

I would go with the following

SELECT SUM(totalprice), year(date), month(date) 
FROM sales
GROUP BY year(date), month(date)

Solution 2

This answer is based on my interpretation of this part of your question:

March, SUM for MArch and the next row should be for April, but there are no items in April yet, so I don't really know how to go about all this.

If you're trying to get all months for a year (say 2013), you need to have a placeholder for months with zero sales. This will list all the months for 2013, even when they don't have sales:

SELECT m.monthnum, SUM(mytable.totalprice)
FROM (
  SELECT 1 AS monthnum, 'Jan' as monthname
  UNION SELECT  2, 'Feb'
  UNION SELECT  3, 'Mar'
  UNION SELECT  4, 'Apr'
  UNION SELECT  5, 'May'
  UNION SELECT  6, 'Jun'
  UNION SELECT  7, 'Jul'
  UNION SELECT  8, 'Aug'
  UNION SELECT  9, 'Sep'
  UNION SELECT 10, 'Oct'
  UNION SELECT 11, 'Nov'
  UNION SELECT 12, 'Dec') m
LEFT JOIN my_table ON m.monthnum = MONTH(mytable.date)
WHERE YEAR(mytable.date) = 2013
GROUP BY m.monthnum
ORDER BY m.monthnum