mysql - monthly sales of each item in a year

Question

Table: orders

orders_id customers_id customers_name date_purchased      orders_status
        1           31 aa AA          2014-08-17 01:31:14             3
        3           32 bb BB          2014-08-17 01:35:47             3
        4           33 cc CC          2014-08-17 01:38:46             3
        5           34 dd DD          2014-08-17 01:41:35             3
        9           35 ee EE          2014-08-17 01:46:18             3
       14           36 fl EE          2014-08-17 02:02:12             3
       15           37 gg GG          2014-08-17 02:09:25             3

Table: orders_products

orders_products_id orders_id products_id products_model products_name products_price final_price products_tax products_quantity
                 1         1          29                Acai Berry          100.0000    100.0000       0.0000 10
                 5         3          30                Vitamin B            50.0000     50.0000       0.0000 20
                 4         3          29                Acai Berry          100.0000    100.0000       0.0000 10
                 6         4          29                Acai Berry          100.0000    100.0000       0.0000 15
                 7         4          30                Vitamin B            50.0000     50.0000       0.0000 10
                 6         5          29                Acai Berry          100.0000    100.0000       0.0000 30
                16         9          29                Acai Berry          100.0000    100.0000       0.0000 20
                15         9          30                Vitamin B            50.0000     50.0000       0.0000 20
                21        14          29                Acai Berry          100.0000    100.0000       0.0000 30
                22        15          30                Vitamin B            50.0000     50.0000       0.0000 30
                23        15          29                Acai Berry          100.0000    100.0000       0.0000 15

Okay, they are the two tables that I'd like to join together to represent all the items sold in 2014 in this format.

products_id, products_name, year, month, total_sales
     1          Acai Berry  2014   01         0
     1          Acai Berry  2014   02         0
     1          Acai Berry  2014   03         0
     1          Acai Berry  2014   04         0
     ....
     1          Acai Berry  2014   12         0
     2          Vitamin B   2014   01         0        
     ... and so on




*Query I was working on - it displays only the months that items were sold.


SELECT
 op.products_id,
 op.products_name,
 IFNULL(t.year, 2014) as year,
 t.month as month,
 ROUND(SUM(op.final_price * op.products_quantity), 2) as total_sales
 FROM
    (SELECT 2014 AS year, 1 AS month UNION 
     SELECT 2014 AS year, 2 AS month UNION 
     SELECT 2014 AS year,3 AS month UNION 
     SELECT 2014 AS year,4 AS month UNION 
     SELECT 2014 AS year,5 AS month UNION
     SELECT 2014 AS year,6 AS month UNION 
     SELECT 2014 AS year,7 AS month UNION 
     SELECT 2014 AS year,8 AS month UNION 
     SELECT 2014 AS year,9 AS month UNION 
     SELECT 2014 AS year,10 AS month UNION 
     SELECT 2014 AS year,11 AS month UNION 
     SELECT 2014 AS year,12 AS month
) AS t 
LEFT JOIN orders o on YEAR(o.date_purchased) = t.year AND MONTH(o.date_purchased) = t.month
LEFT JOIN orders_products op on o.orders_id = op.orders_id
WHERE t.year = 2014 AND o.orders_status = 3
GROUP BY op.products_id, t.month

How can I make it to display all the months even if the items might not be sold at all?

P.S. : If possible, this format would be the better and the best :

              Jan  Feb  Mar  Apr  May Jun July     Aug    Sep Oct Nov Dec
Acai Berry     0    0    0    0    0   0   0     1000.00   0   0   0   0
Vitamin B      0    0    0    0    0   0   0     2000.00   0   0   0   0
More Items...  0    0    0    0    0   0   0        0      0   0   0   0

Any tips or suggestions for me? Thanks in advance.

Kickstart · Answer 1 · 2014-08-19T13:48:13.410

You could do this using SUM(), but with an IF clause within each SUM.

Something like this

SELECT b.product_name
    SUM(IF(YEAR(a.date_purchased) = 2014 AND MONTH(a.date_purchased) = 1), products_quantity, 0) AS Jan,
    SUM(IF(YEAR(a.date_purchased) = 2014 AND MONTH(a.date_purchased) = 2), products_quantity, 0) AS Fed,
    SUM(IF(YEAR(a.date_purchased) = 2014 AND MONTH(a.date_purchased) = 3), products_quantity, 0) AS Mar,
    SUM(IF(YEAR(a.date_purchased) = 2014 AND MONTH(a.date_purchased) = 4), products_quantity, 0) AS Apr,
    SUM(IF(YEAR(a.date_purchased) = 2014 AND MONTH(a.date_purchased) = 5), products_quantity, 0) AS May,
    SUM(IF(YEAR(a.date_purchased) = 2014 AND MONTH(a.date_purchased) = 6), products_quantity, 0) AS Jun,
    SUM(IF(YEAR(a.date_purchased) = 2014 AND MONTH(a.date_purchased) = 7), products_quantity, 0) AS Jul,
    SUM(IF(YEAR(a.date_purchased) = 2014 AND MONTH(a.date_purchased) = 8), products_quantity, 0) AS Aug,
    SUM(IF(YEAR(a.date_purchased) = 2014 AND MONTH(a.date_purchased) = 9), products_quantity, 0) AS Sep,
    SUM(IF(YEAR(a.date_purchased) = 2014 AND MONTH(a.date_purchased) = 10), products_quantity, 0) AS Oct,
    SUM(IF(YEAR(a.date_purchased) = 2014 AND MONTH(a.date_purchased) = 11), products_quantity, 0) AS Nov,
    SUM(IF(YEAR(a.date_purchased) = 2014 AND MONTH(a.date_purchased) = 12), products_quantity, 0) AS Dec
FROM orders a
INNER JOIN orders_products b
ON a.orders_id = b.orders_id
GROUP BY b.product_name

However personally I would prefer to generate the data with a row per item / per month and sort out the formatting for display in the calling script.

EDIT

If producing the list directly then something like this:-

SELECT sub2.product_name, sub1.amonth, SUM(products_quantity)
FROM 
(
    SELECT 2014 AS ayear, 1 AS amonth UNION 
     SELECT 2014, 2 UNION 
     SELECT 2014, 3 UNION 
     SELECT 2014, 4 UNION 
     SELECT 2014, 5 UNION
     SELECT 2014, 6 UNION 
     SELECT 2014, 7 UNION 
     SELECT 2014, 8 UNION 
     SELECT 2014, 9 UNION 
     SELECT 2014, 10 UNION 
     SELECT 2014, 11 UNION 
     SELECT 2014, 12
) sub1
CROSS JOIN 
(
    SELECT DISTINCT product_name
    FROM orders_products
) sub2
LEFT OUTER JOIN orders a
ON YEAR(a.date_purchased) = sub1.ayear
AND MONTH(a.date_purchased) = sub1.amonth
LEFT OUTER JOIN orders_products b
ON a.orders_id = b.orders_id
AND b.product_name = sub2.product_name
GROUP BY sub2.product_name, sub1.amonth

Note that if you had a products table this could be used instead of the second sub query.

Can you come up with a good query for the alternative? Otherwise, I will just rearrange the data from your query using array — Raccoon, Aug 19 '14 at 12:30
The above query should output the data directly in your preferred format. I will have a play and knock up something that would be more efficient but on which you would need to do the formatting in your script. — Kickstart, Aug 19 '14 at 13:41

mysql - monthly sales of each item in a year

1 Answers1