MYSQL sales report query
Posted: Thu Mar 11, 2010 7:55 am
SELECT SUM(ORDER_PRODUCTS_TABLE.price) AS TOTAL_SALE,
DATE_FORMAT(ORDER_DETAILS.created_datetime, '%b') AS month,
DATE_FORMAT(ORDER_DETAILS.created_datetime, '%c') AS month_no
FROM ORDER_PRODUCTS AS ORDER_PRODUCTS_TABLE
LEFT OUTER JOIN ORDER_DETAILS AS ORDER_DETAILS ON(ORDER_PRODUCTS_TABLE.order_id = ORDER_DETAILS.order_id)
WHERE
YEAR(ORDER_DETAILS.created_datetime) = (GIVE CURRENT YEAR)
GROUP BY month ORDER BY ORDER_DETAILS.created_datetime
DATE_FORMAT(ORDER_DETAILS.created_datetime, '%b') AS month,
DATE_FORMAT(ORDER_DETAILS.created_datetime, '%c') AS month_no
FROM ORDER_PRODUCTS AS ORDER_PRODUCTS_TABLE
LEFT OUTER JOIN ORDER_DETAILS AS ORDER_DETAILS ON(ORDER_PRODUCTS_TABLE.order_id = ORDER_DETAILS.order_id)
WHERE
YEAR(ORDER_DETAILS.created_datetime) = (GIVE CURRENT YEAR)
GROUP BY month ORDER BY ORDER_DETAILS.created_datetime