samedi 18 juin 2016

MySQL: fetch data with sum for last 12 month

I'm trying to generate report from database and I have problem with query. So:

I have three tables:

orders (with order_id, order_date, order_total, clients.client_id, shippings.ship_id), clients and shippings

Now I want to fetch orders.*, clients.*, shippings.* as one row which I can achieve with left join like:

SELECT a.*, b.*, c.* FROM orders AS a
LEFT JOIN clients AS b ON a.client_id = b.client_id
LEFT JOIN shippings AS c ON a.ship_id = c.ship_id

But for that row I want to include 12 columns (i.e. month01, month02, month03) which will have sum of orders.order_total for last 12 months assigned by orders.order_date.

I can achieve this via getting all [order, client, shipping] rows and then individually for each row by quering this:

SELECT DATE_FORMAT(order_date, '%m.%Y') AS order_month,
SUM(order_total) AS order_sum FROM orders
WHERE client_id = '.intval($clientID).'
AND ship_id = '.intval($shipID).'
AND order_date >= DATE_SUB(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL 12 MONTH)
GROUP BY order_month;

But this gives me another 12 rows of data with two columns: order_month and order_sum

Is there a way to fetch all of this in one query with months included in one row?

EDIT: SAMPLE DATA (create for last 4 months here):

http://sqlfiddle.com/#!9/139c6

Aucun commentaire:

Enregistrer un commentaire