Query count the INSERT of a month

Question

Query count the INSERT of a month

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

I want to count the INSERTs made in a month, for that at INSERT I have a field called date $fecha=date("n/Y"); but I want to count how many INSERT I made on that date, and then show it on a graph with Morris.JS X seria la el mes y año e y seria la suma de insert de esa fecha I have the following code

<script>
new Morris.Line({
  // ID of the element in which to draw the chart.
  element: 'myfirstchart',
  data: [
    <?php echo $chart_data ?>
  ],
  // x fecha de  ingreso.
  xkey: 'fecha',
  // y contar los datos de ingreso de tal fecha.
  ykeys: [''],
  labels: ['Ingresados']
});
</script>

    SELECT DISTINCT (fecha)
    FROM trabajador 
    WHERE fecha 
    IN (SELECT fecha 
    FROM trabajador 
GROUP BY fecha 
HAVING count( fecha ) >0) 
ORDER BY fecha DESC;

<?php 
//index.php
include('include/conexion.php');
$query = "SELECT DISTINCT (fecha) FROM trabajador WHERE fecha IN (SELECT fecha FROM trabajador GROUP BY fecha HAVING count( fecha ) >0) ORDER BY fecha DESC;";
$result = mysqli_query($conn, $query);
$chart_data = '';
while($row = mysqli_fetch_array($result))
{
 $chart_data .= "{ fecha:'".$row["fecha"]."'}, ";
}
$chart_data = substr($chart_data, 0, -2);
?>

my database

when executing I wish to know

-09/2017, 1
-11/2017, 1 -10/2017, 3

mysql sql php

asked by MoteCL 11.10.2017 в 18:42

source

2 answers

Error # 8134 (Divide by zero error encountered) - SQL Server database replication request.getHeader ("user-agent") JSP

score 0 · Answer 1

I think it could be done this way, but I do not know if it works with the database engine that I'm using, but basically that's what they have to do:

     SELECT YEAR(fecha) AS anio, MONTH(fecha) AS mes, COUNT(*) AS                   
     cantidad_registros
     FROM trabajador 
     GROUP BY 1,2
     ORDER BY 3 DESC;

score 0 · Answer 2

we have to convert the string into a date that can be sorted

SELECT date (concat_ws ('-', SUBSTRING (date, 4), SUBSTRING (date, 1,2), '01')) as f , count (f) as c FROM worker GROUP BY f HAVING c > 0 ORDER BY date DESC;