Add values from the database column

Question

I’m making a reporting system and the bank will have, for example:

> - visualizacoes | 300
> - clicks | 20
> - data | 2015-06-03

> - visualizacoes | 230
> - clicks | 3
> - data | 2015-06-03

> - visualizacoes | 340
> - clicks | 10
> - data | 2015-07-01

So my problem is, it will have 2 records or more the same date (Mźs), as in the example above that has two with the month 06. I need to add the clicks and views of the same month and display 1 result only (already added) in query.

I have the code

$relatorios = $this->db->query("SELECT visualizacoes, clicks, MONTH(data) AS mes FROM relatorio WHERE YEAR(data) = '".date('Y')."' ORDER BY data ASC");

But this one is just organizing to do the report of the current year, but I have no idea how to put something to add the same month.

Answer 1

SELECT
  SUM(visualizacoes),
  SUM(clicks),
  MONTH(data) AS mes
FROM relatorio
WHERE YEAR(data) = '2015'
GROUP BY mes
ORDER BY mes ASC

SQL Fiddle here.

Magic happens in the GROUP BY. What it does, roughly speaking, is that it takes the result of your query, separates the rows into groups based on the columns you specified, and executes "Aggregate functions" (I imagine it is "aggregate functions" in English, but I’m not sure) to join the lines of each little group back in one line. In case, as you want to add, you use the SUM (has other functions to find maximums, minimums, averages, ... the complete list is available on mysql documentation).