Query mysql, count records by date [closed]

Navigation
0

I have a little problem.

I need to generate a query in this way: 

+-----------+------+-------+---+---+
| nombreSol | Enero|Febrero|...|Dic|
+-----------+------+-------+---+---+
| pedro     |  1   |   5   |...| 2 |
| juan      |  3   |   6   |...| 4 |
+-----------+------+-------+---+---+

The truth is that I am new to the area and I am looking for how to do it and I do not know how.

I would greatly appreciate your help ..

    
asked by Anahí Reyes 26.01.2017 в 18:32
source

2 answers

1

Depending on the name of the columns and tables, it should be something like: 

SELECT 
    nombre,
    SUM(CASE WHEN MONTH(fecha) == 1 THEN 1 ELSE 0 END) as Enero,
    SUM(CASE WHEN MONTH(fecha) == 2 THEN 1 ELSE 0 END) as Febrero,
    // ----
    SUM(CASE WHEN MONTH(fecha) == 11 THEN 1 ELSE 0 END) as Noviembre,
    SUM(CASE WHEN MONTH(fecha) == 12 THEN 1 ELSE 0 END) as Diciembre
FROM tabla
GROUP BY nombre; // -- o id en caso de ser posible
    
answered by 26.01.2017 / 18:55
source
0

Given the scheme that you put and taking into account that the table where you get the names is related to another, I would do it with nested queries: 

 SELECT a.id_nombre, a.nombre, 
    (SELECT count(*) from tablaB b where b.id_nombre = a.id_nombre AND month(b.fecha) == 1   ) as Enero,
    (SELECT count(*) from tablaB b where b.id_nombre = a.id_nombre AND month(b.fecha) == 2   ) as Febrero,...

  FROM nombres a

This would be my very humble opinion, luck !!.

    
answered by 26.01.2017 в 19:23
Create a new field within a table and enter the value of another but taking the value of a next row Show items from a list linked to manytomanyfield