Displaying a MYSQL display set for a year with monthly totals

Question

I have two tables, sales and costs, each with a costs fields, and a date field of type date.

The rest of the fields are not relevant for what I want to do.

I want to take the totals of each month, and display them together.

I am unsure if most of this should be done in SQL, if it must be one or several queries, of if I must manipulate the data in PHP...

Answer 1

You can achieve this with SUM() and GROUP BY in sql

Answer 2

Try this two queries for each table.

SELECT month(date_field), SUM(cost_field)
FROM Costs 
GROUP BY month(date_field)

SELECT month(date_field), SUM(cost_field)
FROM Sales
GROUP BY month(date_field)

This is for each month.

Do you need the same list with totals for each year?

In that case it would be almost the same query

SELECT year(date_field), SUM(cost_field)
FROM Costs 
GROUP BY year(date_field)

SELECT year(date_field), SUM(cost_field)
FROM Sales
GROUP BY year(date_field)