Returning multiple columns and grouping by month

Question

I have three tables products, sales and costs.

The sales and products table each have a costs field of type decimal and a date field of type date.

Additionally, the sales table has a productname field, which is matched to a productname field in the products tables.

The products table also has a category field.

I want to select all sales and all purchases for each month, and display them in separate columns grouped by month, up until the current year.

Something like the following

            Sales  Purchases

January      100      50
Febuary      100      50
March        100      50
April        100      50
May          100      50
June         100      50
July          30      50

The purchases table is not related to any other table.

However, the sales table productname field only contains products that exist in the products.productname.

I want to add on to the end of my query

WHERE sales.productname=products.productname AND category='Food'

I am unsure how to return multiple columns, sum them for each month, and then group them by month.

I asked something similar here, and took a solution however it returned sales and purchases together in one column.

How would I keep them as separate columns?

Answer 1

Try this, or something similar...

SELECT sal.theMonth, sal.sumSales, pur.sumPurchases
FROM
 (  
   SELECT date_format(theDate, "%Y-%m") AS theMonth, sum(sales) AS sumSales
   FROM sales_table
   GROUP BY theMonth
 ) AS sal
INNER JOIN
 (  
   SELECT date_format(theDate, "%Y-%m") AS theMonth, sum(purchases) AS sumPurchases
   FROM purchases_table
   GROUP BY theMonth
 ) AS pur
ON sal.theMonth = pur.theMonth

Clearly you will need to adjust table and field names as appropriate for you table definitions...

Answer 2

Somewhat similar to Dave's answer but will show both Sales and Purchases for months where either exists:

SELECT theMonth, sum(Sales) as sumSales, sum(Purchases) as sumPurchases
FROM
 ( SELECT date_format(theDate, "%Y-%m") AS theMonth, Cost as Sales, 0 AS Purchases
   FROM sales, products
   WHERE sales.productname=products.productname AND category='Food'
  UNION ALL
   SELECT date_format(theDate, "%Y-%m") AS theMonth, 0 as Sales, Cost as Purchases
   FROM purchases
 ) AS all_costs
group by theMonth;

(Edited following comments)