I need to show the date by MySQL rows and columns and the SQL does not work

Question

I need to show the date by MySQL rows and columns and the SQL does not work

Navigation

#1 by (3 votes)

3

I am having problems when making a date query, for example: I have a column called Date and it is of type date, in which I only store date, time not! because of that date I want to do columns in which I take the months, in rows the years recorded and in the cells show me the times that the month is repeated for example 2018-05-03, 2018-05-12, I would have a row of the year 2018, a column with the month of May and a cell with 2 records

    SELECT
    YEAR(Fecha) AS AÑO,

    IF( MONTH(fecha)=01, COUNT(MONTH(fecha)), "0" ) AS ENERO,
    IF( MONTH(fecha)=02, COUNT(MONTH(fecha)), "0" ) AS Febrero,
    IF( MONTH(fecha)=03, COUNT(MONTH(fecha)), "0" ) AS Marzo,
    IF( MONTH(fecha)=04, COUNT(MONTH(fecha)), "0" ) AS Abril,
    IF( MONTH(fecha)=05, COUNT(MONTH(fecha)), "0" ) AS Mayo,
    IF( MONTH(fecha)=06, COUNT(MONTH(fecha)), "0" ) AS Junio,
    IF( MONTH(fecha)=07, COUNT(MONTH(fecha)), "0" ) AS Julio,
    IF( MONTH(fecha)=08, COUNT(MONTH(fecha)), "0" ) AS Agosto,
    IF( MONTH(fecha)=09, COUNT(MONTH(fecha)), "0" ) AS Septiembre,
    IF( MONTH(fecha)=10, COUNT(MONTH(fecha)), "0" ) AS Octubre,
    IF( MONTH(fecha)=11, COUNT(MONTH(fecha)), "0" ) AS Nomviembre,
    IF( MONTH(fecha)=12, COUNT(MONTH(fecha)), "0" ) AS Diciembre,

    COUNT(1) as TotalMes

    FROM evidencias GROUP BY YEAR(Fecha);

I do not really understand why the data is added to the month of July, if in July I only have 4 records and there are also records in February, March and others and what is doing there is that only in the month of July I is storing all records

mysql phpmyadmin

asked by Grsn Chml 11.07.2018 в 08:30

source

1 answer

how to get OS processes with python Dynamic table with td and tr selectable

score 3 · Accepted Answer

If I have understood you correctly, in reality what you are looking for is to add 1 each time the condition is fulfilled, not to count at the moment in which it is fulfilled. In this case, try SUM() in MySQL .

SELECT
    YEAR(Fecha) AS ANO,

    SUM(IF( MONTH(Fecha)=01, 1, 0 )) AS Enero,
    SUM(IF( MONTH(Fecha)=02, 1, 0 )) AS Febrero,
    SUM(IF( MONTH(Fecha)=03, 1, 0 )) AS Marzo,
    SUM(IF( MONTH(Fecha)=04, 1, 0 )) AS Abril,
    SUM(IF( MONTH(Fecha)=05, 1, 0 )) AS Mayo,
    SUM(IF( MONTH(Fecha)=06, 1, 0 )) AS Junio,
    SUM(IF( MONTH(Fecha)=07, 1, 0 )) AS Julio,
    SUM(IF( MONTH(Fecha)=08, 1, 0 )) AS Agosto,
    SUM(IF( MONTH(Fecha)=09, 1, 0 )) AS Septiembre,
    SUM(IF( MONTH(Fecha)=10, 1, 0 )) AS Octubre,
    SUM(IF( MONTH(Fecha)=11, 1, 0 )) AS Nomviembre,
    SUM(IF( MONTH(Fecha)=12, 1, 0 )) AS Diciembre,

    COUNT(1) as TotalMes

    FROM evidencias R 
     GROUP BY YEAR(Fecha);

I hope it serves you.