Requête: Count avec un IF et Distinct

Version imprimable

Bonjour,

Tout d'abord j'utilise:
Toad for MySQL v.4.1.0.220
MySQL v.5.1

J'aimerai pouvoir compter le nombre de client unique faisant du gratuit et de même avec le payant

Exemple de table:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE TABLE table_billing (
client integer,
billing_date date,
price integer);
INSERT INTO table_billing VALUES (1456, '2009-01-01', 0);
INSERT INTO table_billing VALUES (4542, '2009-01-01', 30);
INSERT INTO table_billing VALUES (1456, '2009-01-01', 10);
INSERT INTO table_billing VALUES (7894, '2009-01-01', 44);
INSERT INTO table_billing VALUES (4585, '2009-01-01', 55);
INSERT INTO table_billing VALUES (1456, '2009-01-01', 0);
INSERT INTO table_billing VALUES (6527, '2009-01-02', 0);
INSERT INTO table_billing VALUES (3658, '2009-01-02', 91);
INSERT INTO table_billing VALUES (7459, '2009-01-02', 1);
INSERT INTO table_billing VALUES (6310, '2009-01-02', 0);
INSERT INTO table_billing VALUES (7459, '2009-01-02', 100);
INSERT INTO table_billing VALUES (6310, '2009-01-02', 0);

Résultat souhaité

Code:

1
2
3
4
 
date           payant  gratuit total
01/01/09	4	1	4
01/01/09	2	2	4

J'ai tenté des syntaxes comme celle-ci mais sans succès

Code:

1
2
3
4
5
6
7
SELECT DATE_FORMAT(billing_date, '%m/%d/%y') AS JOUR,
       Count(distinct if(price>0 and client) AS "client unique payant",
       Count(distinct if(price=0 and client) AS "client unique gratuit",
       Count(distinct client) AS "total client unique"
FROM table_billing
GROUP BY JOUR
ORDER BY JOUR

Pourtant je ne dois pas être loin
Si une âme charitable passe par là, merci d'avance!

Et le résultat de la requête est :

Citation:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AS "client unique payant", Count(DISTINCT IF(price=0 AND client) AS "cl' at line 2

Ce qui signifie que vous avez une erreur de syntaxe juste avant le morceau de requête indiqué. Il manque une parenthèse fermante mais en plus cette syntaxe ne fonctionne pas.

Pas si facile que ça votre problème mais j'y suis arrivé ! :yaisse2:
Le principe est de compter séparément les payants et les gratuits et de faire l'union des requêtes puis de faire la somme par jour.

Au passage, je signale que votre transformation de date fera que s'afficheront dans l'ordre les mois avant les années. Autrement dit, janvier 2010 s'affichera avant février 2009 ! :? J'ai supprimé la transformation pour simplifier la requête.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 SELECT t.billing_date, SUM(t.payant) AS "client unique payant", SUM(t.gratuit) AS "client unique gratuit" FROM ( SELECT billing_date, COUNT(DISTINCT client) AS payant, 0 AS gratuit FROM table_billing WHERE price > 0 GROUP BY billing_date UNION SELECT billing_date, 0, COUNT(DISTINCT client) FROM table_billing WHERE price = 0 GROUP BY billing_date ) AS t GROUP BY t.billing_date

04/11/2009, 09h52
Oishiiii

Bonjour,

En quoi ma réponse donnée précédemment n'est pas bonne?
http://www.developpez.net/forums/d83...e/#post4758235
04/11/2009, 09h57
Sethenssen

Bonjour,

Merci CinePhil je vais essayer cela de suite !

Oishiiii,
La réponse du post précédent était parfaite je vous rassure.
Cependant j'avais une autre intérrogation légèrement differente puisque c'était de compter cette fois en client unique.
04/11/2009, 10h38
Sethenssen

C'est parfait CinePhil !
Procéder par un UNION est simple et efficace tout compte fait.

Cependant il faut que je retransforme la date pour ne pas afficher l'hh:mm:ss car dans ma table c'est inlus et j'ai donc les clients unique par heure :mouarf:

Je vais je pense inclure un DATE_FORMAT pour n'afficher que les dd/mm/yyyy
04/11/2009, 10h43
CinePhil

OK mais si tu veux garder le vrai ordre des dates, laisse le tri par billing_date sans trasnformation sinon tu auras :
01/01/2009
01/02/2009
...

Et applique la transformation seulement sur le premier SELECT. Pour les autres, c'est inutile et ça ralentirait la requête.

Arf, je m'arrache les cheveux avec les formats de date

J'ai updaté la table en ajoutant des heures

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
CREATE TABLE /*!32312 IF NOT EXISTS*/ "table_billing" (
  "client" int(11) DEFAULT NULL,
  "billing_date" datetime DEFAULT NULL,
  "price" int(11) DEFAULT NULL
);
 
 
 
#
# Dumping data for table 'table_billing'
#
 
LOCK TABLES "table_billing" WRITE;
/*!40000 ALTER TABLE "table_billing" DISABLE KEYS;*/
REPLACE INTO "table_billing" ("client", "billing_date", "price") VALUES
	(1456,'2009-01-01 01:10:00',0);
REPLACE INTO "table_billing" ("client", "billing_date", "price") VALUES
	(4542,'2009-01-01 02:00:00',30);
REPLACE INTO "table_billing" ("client", "billing_date", "price") VALUES
	(1456,'2009-01-01 03:00:00',10);
REPLACE INTO "table_billing" ("client", "billing_date", "price") VALUES
	(7894,'2009-01-01 04:00:00',44);
REPLACE INTO "table_billing" ("client", "billing_date", "price") VALUES
	(4585,'2009-01-01 05:00:00',55);
REPLACE INTO "table_billing" ("client", "billing_date", "price") VALUES
	(1456,'2009-01-01 01:10:00',0);
REPLACE INTO "table_billing" ("client", "billing_date", "price") VALUES
	(6527,'2009-01-02 01:00:00',0);
REPLACE INTO "table_billing" ("client", "billing_date", "price") VALUES
	(3658,'2009-01-02 02:00:00',91);
REPLACE INTO "table_billing" ("client", "billing_date", "price") VALUES
	(7459,'2009-01-02 03:00:00',1);
REPLACE INTO "table_billing" ("client", "billing_date", "price") VALUES
	(6310,'2009-01-02 04:00:00',0);
REPLACE INTO "table_billing" ("client", "billing_date", "price") VALUES
	(7459,'2009-01-02 00:00:00',100);
REPLACE INTO "table_billing" ("client", "billing_date", "price") VALUES
	(6310,'2009-01-02 04:00:00',0);
/*!40000 ALTER TABLE "table_billing" ENABLE KEYS;*/
UNLOCK TABLES;

Avec votre requête j'ai ajouté un DATE_FORMAT:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT DATE_FORMAT(t.billing_date, '%m/%d/%y') AS JOUR, 
  SUM(t.payant) AS "client unique payant",
  SUM(t.gratuit) AS "client unique gratuit"
FROM (
  SELECT billing_date, COUNT(DISTINCT client) AS payant, 0 AS gratuit
  FROM table_billing
  WHERE price > 0
  GROUP BY billing_date
 
  UNION
 
  SELECT billing_date, 0, COUNT(DISTINCT client)
  FROM table_billing
  WHERE price = 0
  GROUP BY billing_date
) AS t
GROUP BY t.billing_date;

En résultat il me donne le nombre de client unique par heure ou minute.
J'ai donc essayé d'appliquer cette même transformation dans le distinct avec le même résultat ou des erreurs de syntaxe

Pour la date vous avez raison avec des années differentes l'affiche sera assez moche. Mais pour le cas présent j'ai laissé tel quel

Code:

1
2
3
4
5
6
7
8
9
10
11
JOUR	client unique payant	client unique gratuit
01/01/09	0	1
01/01/09	1	0
01/01/09	1	0
01/01/09	1	0
01/01/09	1	0
01/02/09	1	0
01/02/09	0	1
01/02/09	1	0
01/02/09	1	0
01/02/09	0	1

J'ai réussi à m'en sortir avec le DATE_FORMAT

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT DATE_FORMAT(t.billing_date, '%m/%d/%y') AS JOUR, 
  SUM(t.payant) AS "client unique payant",
  SUM(t.gratuit) AS "client unique gratuit"
FROM (
  SELECT billing_date, COUNT(DISTINCT client) AS payant, 0 AS gratuit
  FROM table_billing
  WHERE price > 0
  GROUP BY DATE_FORMAT(billing_date, '%m/%d/%y')
 
  UNION
 
  SELECT billing_date, 0, COUNT(DISTINCT client)
  FROM table_billing
  WHERE price = 0
  GROUP BY DATE_FORMAT(billing_date, '%m/%d/%y')
) AS t
GROUP BY JOUR;

04/11/2009, 13h35
CinePhil

Avec ORDER BY t.billing_date pour retrouver un bon tri comme vous le souhaitiez au début, ce sera parfait ! :ccool: