COUNT et GROUP BY avec clause WHERE

[mickey45]

bonjour,
un problème souvent discuté mais auquel je ne trouve pas de réponse:

j'ai une table clients comme suit:
id : date_creation

je veux connaitre le nombre d'inscriptions par jour, entre deux dates

Voici la requète:

Code :

1
2
3
4
SELECT from_unixtime(date_creation,'%Y %M %D'),COUNT(id) FROM `clients` 
WHERE date_creation >= '".$startTime."' AND  date_creation <= '".$endTime."' 
GROUP BY from_unixtime(date_creation,'%Y %M %D') 
ORDER BY date_creation ASC

qui renvoie par exemple:

Code :

1
2
3
4
5
6
2011 August 1st 2
2011 August 2nd 3
2011 August 3rd 2
2011 August 4th 2
2011 August 5th 4
2011 August 8th 4

le problème étant que la requête n'envoie pas de 0 pour les jours sans inscriptions (les 6 et 7 Aout par exemple) ceci à cause de la clause where

la seule solution est de faire des tables de jours des mois avec un join dessus ?

Merci pour vos conseils éclairés

Note: la structure de la table est ce qu'elle est, (champs date qui est un time() dans un varchar)

[ced]

Bonjour,

Envoyé par mickey45

la seule solution est de faire des tables de jours des mois avec un join dessus ?

Oui, il te faut une table pivot avec toutes les dates sur laquelle t'appuyer pour pouvoir faire ça.
Une table temporaire générée avant chaque requête, par exemple.

[fab256]

Tu pourrais créer une table de 0 a 9 et avec des CROSS JOIN sur cette meme table pour avoir une table d'entier. Ajouter chaque entier a la date de départ et vérifier si elle ne dépasse pas la date d'arrivée.

Code :

1
2
CREATE TABLE num (i int);
INSERT INTO num (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

Ensuite tu fait un LEFT JOIN sur la colonne date_creation

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT a.date_creation, b.nombre FROM
(SELECT adddate( '2011-08-01', numlist.id ) AS date_creation
FROM (
SELECT n1.i + n10.i *10 + n100.i *100 AS id
FROM num n1
CROSS JOIN num AS n10
CROSS JOIN num AS n100
) AS numlist
WHERE adddate( '2011-08-01', numlist.id ) <= '2011-08-31') a
LEFT JOIN
(SELECT date_creation, COUNT(id) AS nombre FROM clients
WHERE date_creation BETWEEN '2011-08-01' AND '2011-08-31' 
GROUP BY date_creation
ORDER BY date_creation ASC
) AS b
ON a.date_creation=b.date_creation

On peux aussi créer une table d'entier ( de 0 a n) une fois pour toute et éviter les CROSS JOIN. (n: maximum de difference entre deux dates)

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT a.date_creation, b.nombre
FROM (
    SELECT ADDDATE( '2011-08-01', i ) AS date_creation
    FROM Num
    WHERE i BETWEEN 0 AND DATEDIFF('2011-08-31', '2011-08-01')
) AS a
LEFT JOIN 
(SELECT date_creation, COUNT(id) AS nombre FROM clients
WHERE date_creation BETWEEN '2011-08-01' AND '2011-08-31' 
GROUP BY date_creation
ORDER BY date_creation ASC
) AS b
ON a.date_creation = b.date_creation

[Yanika_bzh]

Envoyé par fab256

Tu pourrais créer une table de 0 a 9 pour générer la liste des dates de ton intervale

Mince, moi qui croyais qu'une année comportait 12 mois !

Sinon, y'a surement de quoi faire votre bonheur dans ce petit papier

[fab256]

On peux encore faire mieux! Sans table temporaire, sans boucle, et sans procédure.
On remplace la table num dans le code précédent

Code :

1
2
CREATE TABLE num (i int);
INSERT INTO num (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

Par

Code :

1
2
3
4
5
6
7
8
9
10
SELECT 0 AS i
UNION ALL SELECT 1
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

C'est fou! Le code devient

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT tab1.date_creation, tab2.nombre FROM
(
SELECT adddate( '2011-08-01', numlist.id ) AS date_creation
FROM (
SELECT a.i + b.i *10 + c.i *100 AS id
FROM (SELECT 0 AS a UNION ALL SELECT 1 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) a
CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 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) AS b
CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 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) AS c
) AS numlist
WHERE adddate( '2011-08-01', numlist.id ) <= '2011-08-31') tab1
 
LEFT JOIN
(SELECT date_creation, COUNT(id) AS nombre FROM clients
WHERE date_creation BETWEEN '2011-08-01' AND '2011-08-31' 
GROUP BY date_creation
ORDER BY date_creation ASC
) AS tab2
ON tab1.date_creation=tab2.date_creation

La différence entre deux dates dans cette exemple ne peux dépasser 999 jours, il faut rajouter d'autres cross join si la différence peut etre plus grande.

[mickey45]

vos réponses sont très instructives, merci

j'ai testé les 3 requêtes, elles ne renvoient aucune erreur mais des NULL pour nombre (d'inscrits) pour toutes les dates (sauf le 3 août à vrai dire)
hors un

Code :

SELECT id,nom FROM clients WHERE datetime_creation BETWEEN '2011-08-01' AND '2011-08-31'

me renvoie bien des inscriptions

je ne comprends pas d'où ça vient
Merci à tous

[fab256]

T'es sur que c'est datetime_creation ou bien date_creation???

[mickey45]

oui pardon je n'ai pas précisé,
en fait le champs datetime_creation est en format datetime mysql
donc toujours le même problème

[fab256]

Le format du type DATETIME est 'YYYY-MM-DD HH:MM:SS' Alors que le type DATE est 'YYYY-MM-DD'. Si tu veux avoir un groupage par date, Il faut convertir datetime_creation qui est de type DATETIME en type DATE.

Code :

CAST(datetime_creation AS DATE)