COUNT et GROUP BY avec clause WHERE

Version imprimable

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)

29/08/2011, 12h41
ced

Bonjour,

Citation:

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.

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

29/08/2011, 14h43
Yanika_bzh

Citation:

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 !:mouarf:

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

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.

13/09/2011, 12h17
mickey45

Merci à tous

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
13/09/2011, 14h06
fab256

T'es sur que c'est datetime_creation ou bien date_creation???
13/09/2011, 14h18
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 :(
13/09/2011, 14h31
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)