MySQL get quantities from last 12 months grouped by month

Question

I'm trying to write a MySQL SELECT statement in PHP.

I have 2 tables, sales and sale_items.

sales has columns: sale_id, status

sale_items has columns: sale_id, date_added (DATETIME), quantity

I need to return the quantities from sale_items, over the last 12 months grouped by month and where the status of the corresponding row in sales is equal to 'completed' (as you can see, sales and sale_items can be joined by sale_id).

I have tried modifying both of the following to suit my needs, but with no luck:

MySQL monthly Sale of last 12 months including months with no Sale

Mysql sum for last 12 months

score 1 · Accepted Answer · answered Oct 08 '16 at 09:31

1

It's very easy you can use MySQL MONTH() function for this along with GROUP BY caluse.

SELECT SUM(SI.quantity),MONTH(SI.date_added)
FROM sale_items SI
JOIN sales S
ON S.id=SI.sale_id
WHERE S.status = 'completed'
GROUP BY MONTH(SI.date_added);

answered Oct 08 '16 at 09:31

Alok Patel

7,842
5
31
47

Well this makes certain, grave assumptions about the dataset – Strawberry Oct 08 '16 at 09:33
I assume you're talking about date field, Isn't it? – Alok Patel Oct 08 '16 at 09:36
Yes, the `date_added` field is the one to be used to group, I will give this a try shortly – sgspragg Oct 08 '16 at 10:09
Works a treat, can't believe I thought this would be harder than it was – sgspragg Oct 08 '16 at 10:23

score 0 · Answer 2 · answered Oct 08 '16 at 09:34

0

SELECT COUNT(quantity),MONTH(SI.date_added)
FROM sale_items SI
JOIN sales S
ON S.id=SI.sale_id
WHERE S.status = 'completed'
GROUP BY MONTH(SI.date_added);

answered Oct 08 '16 at 09:34

MounirOnGithub

669
1
9
22

While this code snippet may solve the question, [including an explanation](http://meta.stackexchange.com/questions/114762/explaining-entirely-code-based-answers) really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion. – andreas Oct 08 '16 at 10:21
Ok I will explane more next time sorry. – MounirOnGithub Oct 08 '16 at 11:19

MySQL get quantities from last 12 months grouped by month

2 Answers2

Linked