Adding values from the database column

Question

Adding values from the database column

Navigation

#1 by (1 votes)

1

I'm doing 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's going to have 2 records or more of the date (Ms) the same 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 is only organizing to do the report of the current year, but I have no idea how to put something to add the same month.

php mysql date

asked by anonymous 04.06.2015 / 17:10

1 answer

Element with onClick and onDblClick I can not create a new Activity

score 1 · Accepted Answer

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 .

The magic happens in GROUP BY . What it does, roughly, is that it takes the result of your query, separates the rows into clusters based on the columns you specified, and executes "aggregate functions" (I imagine it to be "aggregate functions" in Portuguese but not I'm sure) to join the lines of each group back in a row. In case, as you want to add, you use SUM (you have other functions to find maximums, minimums, averages, ... the complete list is available at MySQL documentation ).