CODEWITHSUNDEEP
mysqlsqldategroup-by
James B
James B

Reputation: 57

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:

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

Upvotes: 3

Views: 26025

Answers (3)

Kenneth M. Nielsen
Kenneth M. Nielsen

Reputation: 487

I would go with the following

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

Upvotes: 7

Ed Gibbs
Ed Gibbs

Reputation: 26333

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269463

If I understand what you mean, you can put logic in a case statement and use that in a group by:

select (case when month(date) = 1 then 'Jan'
             when month(date) = 2 then 'Feb'
             when month(date) = 3 then 'Mar'
             else 'Other'
        end) as period, sum(quantity) as sumquantity
from t
group by (case when month(date) = 1 then 'Jan'
             when month(date) = 2 then 'Feb'
             when month(date) = 3 then 'Mar'
             else 'Other'
        end)

Upvotes: 0

Related Questions