Requête Mysql pour obtenir comte par mois

J'ai une table pour mes utilisateurs qui ont un champ nommé "créé" qui ont de la date d'enregistrement.

Comment puis-je obtenir une liste qui contient un nombre pour le nombre d'enregistrements par mois dans les 12 derniers mois?
Comme ceci:

Month   Count
1        1232
2        2222
3         122
4        4653
...       ...
12       7654

Je n'ai pas l'habitude de travailler avec mysql, jusqu'à maintenant, je sais juste comment compter le nombre d'enregistrements dans la dernière année, pas sur la façon de groupe de comptage par 12 derniers mois. Merci à l'avance!

Mise à JOUR

Maintenant, je reçois ce, à l'aide de @fthiella solution:

+------------------------------+-------------------------------+----------+
| Year(FROM_UNIXTIME(created)) | Month(FROM_UNIXTIME(created)) | Count(*) |
+------------------------------+-------------------------------+----------+
|                         2012 |                             4 |     9927 |
|                         2012 |                             5 |     5595 |
|                         2012 |                             6 |     4431 |
|                         2012 |                             7 |     3299 |
|                         2012 |                             8 |      429 |
|                         2012 |                            10 |     3698 |
|                         2012 |                            11 |     6208 |
|                         2012 |                            12 |     5142 |
|                         2013 |                             1 |     1196 |
|                         2013 |                             2 |       10 |
+------------------------------+-------------------------------+----------+

Comment puis-je la force de la requête de me donner le mois avec count = 0?

Solution par @fthiella (merci beaucoup!):

     SELECT y, m, Count(users.created)
     FROM (
      SELECT y, m
      FROM
         (SELECT YEAR(CURDATE()) y UNION ALL SELECT YEAR(CURDATE())-1) years,
         (SELECT 1 m UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
           UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
           UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12) months) ym
       LEFT JOIN users
       ON ym.y = YEAR(FROM_UNIXTIME(users.created))
          AND ym.m = MONTH(FROM_UNIXTIME(users.created))
     WHERE
       (y=YEAR(CURDATE()) AND m<=MONTH(CURDATE()))
       OR
       (y<YEAR(CURDATE()) AND m>MONTH(CURDATE()))
     GROUP BY y, m;

Et les résultats:

+------+----+----------------------+
| y    | m  | Count(users.created) |
+------+----+----------------------+
| 2012 |  5 |                 5595 |
| 2012 |  6 |                 4431 |
| 2012 |  7 |                 3299 |
| 2012 |  8 |                  429 |
| 2012 |  9 |                    0 |
| 2012 | 10 |                 3698 |
| 2012 | 11 |                 6208 |
| 2012 | 12 |                 5142 |
| 2013 |  1 |                 1196 |
| 2013 |  2 |                   10 |
| 2013 |  3 |                    0 |
| 2013 |  4 |                    0 |
+------+----+----------------------+

OriginalL'auteur David R | 2013-04-02

S'est créé un champ INT, vous devez utiliser FROM_UNIXTIME fonction pour convertir un champ de date, puis MOIS fonction pour extraire le mois:

SELECT Month(FROM_UNIXTIME(created)), Count(*)
FROM yourtable
WHERE FROM_UNIXTIME(created) >= CURDATE() - INTERVAL 1 YEAR
GROUP BY Month(FROM_UNIXTIME(created))

cela permettra de compter toutes les lignes qui ont été créés au cours des 12 derniers mois. Veuillez noter qu'il est probablement mieux de aussi groupe par l'ANNÉE:

SELECT Year(FROM_UNIXTIME(created)), Month(FROM_UNIXTIME(created)), Count(*)
FROM yourtable
WHERE FROM_UNIXTIME(created) >= CURDATE() - INTERVAL 1 YEAR
GROUP BY Year(FROM_UNIXTIME(created)), Month(FROM_UNIXTIME(created))

Si vous avez besoin de compter les numéros d'enregistrement au lieu de lignes, vous pouvez utiliser quelque chose comme

COUNT(registration_number)

d'ignorer les valeurs null, ou

COUNT(DISTINCT registration_number)

pour compter uniquement distinctes.

Modifier

Si vous avez aussi besoin de montrer des mois qui ont count=0, je voudrais utiliser une requête de ce qui retourne tous les mois pour le présent et pour l'année précédente:

SELECT y, m
FROM
  (SELECT YEAR(CURDATE()) y UNION ALL SELECT YEAR(CURDATE())-1) years,
  (SELECT 1 m UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
    UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
    UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12) months;

Et puis je utiliser une JOINTURE GAUCHE, qui retourne toutes les lignes de la première requête, et que les lignes de la deuxième requête qui correspond à:

SELECT y, m, Count(yourtable.created)
FROM (
  SELECT y, m
  FROM
    (SELECT YEAR(CURDATE()) y UNION ALL SELECT YEAR(CURDATE())-1) years,
    (SELECT 1 m UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
      UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
      UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12) months) ym
  LEFT JOIN yourtable
  ON ym.y = YEAR(FROM_UNIXTIME(yourtable.created))
     AND ym.m = MONTH(FROM_UNIXTIME(yourtable.created))
WHERE
  (y=YEAR(CURDATE()) AND m<=MONTH(CURDATE()))
  OR
  (y<YEAR(CURDATE()) AND m>MONTH(CURDATE()))
GROUP BY y, m

(notez qu'ici, je suis en considérant seulement les 12 derniers mois, de sorte que si nous sommes au milieu du mois d'avril 2013, il va compter les lignes dans l'intervalle Mai 2012 - le 13 avril, si ce n'est pas le comportement correct s'il vous plaît laissez-moi savoir)

mes créé champ est un champ int parce que ma date d'inscription est dans le format d'horodatage...
MONTH(FROM_UNIXTIME(your_field)) devrait fonctionner
J'ai mis à jour ma réponse, non il devrait être correct
Il fonctionne! Merci 🙂
Comment puis-je la force de la requête de me donner le mois avec count = 0?

OriginalL'auteur fthiella

9
```
SELECT MONTH(reg_date) , COUNT(reg_date) 
FROM your_table
WHERE reg_date >= NOW() - INTERVAL 1 YEAR
GROUP BY MONTH(reg_date)
```
Son affiche pour le Mois dont les données sont disponibles dans la Base de données. Comment aurais-je le faire pour tous les mois. Moyens de 1 à 12 mois. ??
Case à l'autre de répondre 🙂

OriginalL'auteur Vatev

Dans le cas où cette aide à quelqu'un d'autre, j'en outre voulais cette requête, mais pour un intervalle de temps donné qui n'était pas forcément des 12 derniers mois (également avec un timestamp de la date):

set @start='2013-05-01 00:00:00';
set @end='2015-03-31 00:00:00';

SELECT YEAR(FROM_UNIXTIME(yourtable.created_date)), MONTH(FROM_UNIXTIME(yourtable.created_date)), COUNT(yourtable.created_date) 
FROM yourtable
WHERE created_date BETWEEN UNIX_TIMESTAMP( DATE(@start) ) AND UNIX_TIMESTAMP( DATE(@end) )
GROUP BY YEAR(FROM_UNIXTIME(yourtable.created_date)), MONTH(FROM_UNIXTIME(yourtable.created_date))
ORDER BY YEAR(FROM_UNIXTIME(yourtable.created_date)), MONTH(FROM_UNIXTIME(yourtable.created_date));

OriginalL'auteur David M

Vous devez vous connecter pour publier un commentaire.