James B James B - 4 months ago 8
SQL Question

SQL query to retrieve SUM in various DATE ranges

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.

Any help is much appreciated.

Answer

I would go with the following

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