COUNT avec deux WHERE différents

Version imprimable

Bonjour!
Dans une requête, je récupère les titres d'ouvrages auxquels sont associés des livres à vendre et des vendeurs. Je veux afficher le nombre de livre invendu pour les vendeurs dont le compte est actif et dont le compte est inactif.

Pour vous épargner les détails, ce que je veux faire en gros est de compter les lignes d'une même table avec deux condition WHERE différentes, le tout étant imbriqué en sous-requêtes.

Bref, si mes explications ne sont pas claires, j'ai déjà un code qui fonctionne bien mais je tente de l'optimiser et de le raccourcir :

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
41
42
43
44
SELECT
   O.idOuvrage,
   O.titre,
 
   COUNT(DISTINCT idLivre) AS nbreLivres,
 
   -- Livres invendus, des vendeurs dont le compte est actif
   (SELECT COUNT(idLivre)
      FROM
         livres AS L
         LEFT JOIN vendeurs AS E         ON L.idVendeur = E.idVendeur
      WHERE
         dateVente IS NULL AND
         idOuvrage = O.idOuvrage AND
         -- TRUE pour un compte actif, FALSE pour un compte inactif
         DATEDIFF(DATE_ADD(dateDerniereActivite, INTERVAL 2 YEAR), NOW()) >= 0
      ) AS nbreLivresActifs,
 
   -- Livres invendus des vendeurs dont le compte est inactif
   -- Notez que la sous-requête ci-dessous est quasiment identique à
   -- la première à l'exception de la condition WHERE
   (SELECT COUNT(idLivre)
      FROM
         livres AS L
            LEFT JOIN vendeurs AS E      ON L.idVendeur = E.idVendeur
      WHERE
         dateVente IS NULL AND
         idOuvrage = O.idOuvrage AND
         -- TRUE pour un compte inactif, FALSE pour un compte actif
         DATEDIFF(DATE_ADD(dateDerniereActivite, INTERVAL 2 YEAR), NOW()) < 0
      ) AS nbreLivresInactifs,
 
   GROUP_CONCAT(DISTINCT 
      CONCAT_WS(' ', A.prenom, A.nom)
      ORDER BY A.nom
      SEPARATOR ', '
      ) AS listeAuteurs      
   FROM ouvrages AS O
      LEFT JOIN livres AS L         ON O.idOuvrage = L.idOuvrage
      LEFT JOIN ouvrages_auteurs AS OA   ON O.idOuvrage = OA.idOuvrage
      LEFT JOIN auteurs AS A         ON OA.idAuteur = A.idAuteur
   WHERE L.dateVente IS NULL
   GROUP BY O.idOuvrage
   ORDER BY O.titre

Merci à l'avance de votre aide!

La question est longue mais je crois bien que quelqu'un de compétent puisse y répondre sans stagner pendant des heures comme je viens de le faire!

14/02/2009, 07h23
Antoun
Normalement, ça se fait sans sous-requête, sur ce modèle :
Code:

1 2 SUM(CASE WHEN condition1 THEN 1 ELSE 0 END) AS Nb1, SUM(CASE WHEN condition2 THEN 1 ELSE 0 END) AS Nb2,
Sur ta requête, tu ne montres pas d'où viennent plusieurs colonnes de tes sous-requêtes (idOuvrage, dateDerniereActivite), donc je ne peux pas te donner la transposition.

Salut Antoun,

La solution que tu m'as proposé fonctionne bien. Toutefois, la jointure que je fais avec la tables de parité ouvrages_auteurs a pour effet de multiplier le résultat de la somme par le nombre d'auteurs. La seule solution que j'ai trouvé est de diviser le résultat de la somme par le nombre d'auteur mais je me doute qu'il y a une meilleure solution.

De plus, sachant que nbreLivresActifs + nbreLivresInactifs = nbreLivres, crois-tu qu'il y a une manière de raccourcir ce code?

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
SELECT
      O.idOuvrage,
      O.titre,
 
      GROUP_CONCAT(DISTINCT
         CONCAT_WS(' ', A.prenom, A.nom)
         ORDER BY A.nom
         SEPARATOR ', '
      ) AS listeAuteurs,
 
      COUNT(DISTINCT L.idLivre) AS nbreLivres,
 
      -- Livres invendus des vendeurs dont le compte est actif
      ROUND(SUM(
         CASE
            WHEN DATEDIFF(DATE_ADD(V.dateDerniereActivite, INTERVAL 2 YEAR), NOW()) >= 0
            THEN 1
            ELSE 0
         END
      )/COUNT(DISTINCT A.nom)) AS nbreLivresActifs,
 
      -- Livres invendus des vendeurs dont le compte est inactif
      ROUND(SUM(
         CASE
            WHEN DATEDIFF(DATE_ADD(V.dateDerniereActivite, INTERVAL 2 YEAR), NOW()) < 0
            THEN 1
            ELSE 0
         END
      )/COUNT(DISTINCT A.nom)) AS nbreLivresInactifs
 
   FROM ouvrages AS O
      LEFT JOIN livres AS L         ON O.idOuvrage = L.idOuvrage
      LEFT JOIN vendeurs AS V      ON L.idVendeur = V.idVendeur
      LEFT JOIN ouvrages_auteurs AS OA   ON O.idOuvrage = OA.idOuvrage
      INNER JOIN auteurs AS A         ON OA.idAuteur = A.idAuteur
 
   WHERE L.dateVente IS NULL
   GROUP BY O.idOuvrage
   ORDER BY O.titre

15/02/2009, 15h31
Antoun
sans trop regarder ta requête, je te suggèrerais un truc comme ça :
Code:

1 2 COUNT(DISTINCT CASE WHEN condition THEN IdLivre ELSE NULL END)

Merci Antoun!

Ton apport sur ce forum est inestimable, merci encore un fois pour ton aide claire et efficace! :king:

J'ai utilisé le code suivant, et ça fonctionne très bien :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
		-- Livres invendus des vendeurs dont le compte est actif
		COUNT(DISTINCT
			CASE
			WHEN DATEDIFF(DATE_ADD(E.dateDerniereActivite, INTERVAL 2 YEAR), NOW()) >= 0
			THEN L.idLivre
			ELSE NULL
			END
		) AS nbreLivresActifs,
 
		-- Livres invendus des vendeurs dont le compte est inactif
		COUNT(DISTINCT
			CASE
			WHEN DATEDIFF(DATE_ADD(E.dateDerniereActivite, INTERVAL 2 YEAR), NOW()) < 0
			THEN L.idLivre
			ELSE NULL
			END
		) AS nbreLivresInactifs,
 
		-- Livres invendus, au total (équivalent à « nbreLivresActifs + nbreLivresInactifs »)
		COUNT(DISTINCT L.idLivre) AS nbreLivres