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):
Aucun commentaire:
Enregistrer un commentaire