COUNT, SUM et jointures

Version imprimable

Bonjour,

Je me casse un peu la tête sur une requête :)

Je voudrais en une seule requête récupérer des stats utilisateurs.

Imaginons qu'un utilisateur puisse avoir des albums photos, des albums vidéos.

Chaque table associée possède un view_count et un comment_count.

Je veux récupérer le nombre d'albums photos, le nombre d'albums vidéos, ainsi que le cumul total du nombre de vues tout confondu et le cumul total du nombre de commentaires tout confondu.

Admettons les données suivantes :

user_id = 2
1 album photo avec 5 vues | 0 com
3 albums vidéos avec :
- 15 vues | 2 com
- 4 vues | 3 com
- 12 vues | 0 com

Voici un début de requête :
Code:

1 2 3 4 5 6 7 8 9 10 SELECT u . * , COUNT( DISTINCT pa.id ) AS photoAlbumCount, COUNT( DISTINCT ma.id ) AS movieAlbumCount, ( SUM( COALESCE( pa.view_count, 0 ) ) + SUM( COALESCE( ma.view_count, 0 ) ) ) AS viewCount, ( SUM( COALESCE( pa.comment_count, 0 ) ) + SUM( COALESCE( ma.comment_count, 0 ) ) ) AS commentCount FROM users u LEFT JOIN photo_albums pa ON pa.user_id = u.id LEFT JOIN movie_albums ma ON ma.user_id = u.id WHERE u.id = 2;
Le pb c'est que comme j'ai 3 albums vidéos, le nombre de vues de l'album photo va être compté 3x et au lieu d'avoir un viewCount égal à 36 (5+15+4+12), j'aurais le viewCount égal à 46 (5+5+5 +15+4+12).

Je sais pas si c'est possible de remédier à ça via MySQL uniquement :)

Des idées ?

Merci,
A+ benjamin.

Bonjour,

Vous pouvez utiliser des "tables dérivées", ce sont des sous-requêtes de la clause FROM.

A tester:

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
 SELECT 
   u.id,
   COALESCE(nbPhoto, 0) AS nbPhoto,
   COALESCE(nbMovie, 0) AS nbMovie,
   COALESCE(pho.nbCom, 0) + COALESCE(mov.nbCom, 0) AS commentCount,
   COALESCE(pho.nbView, 0) + COALESCE(mov.nbView, 0) AS viewCount
 FROM users AS u
   LEFT JOIN (
     SELECT 
       user_id, 
       Count(*) AS nbPhoto, 
       SUM(view_count) AS nbCom,
       SUM(comment_count) AS nbView
     FROM photo_albums
     GROUP BY user_id
   ) AS pho
     ON u.id = pho.user_id
   LEFT JOIN (
     SELECT 
       user_id, 
       Count(*) AS nbMovie, 
       SUM(view_count) AS nbCom,
       SUM(comment_count) AS nbView
     FROM movies_albums
     GROUP BY user_id
   ) AS mov
     ON u.id = mov.user_id
WHERE u.id = 2

30/06/2010, 15h43
Delprog

Salut Oishiiii,

Merci pour ton aide, ta requête m'a bien aidé, je l'a complété pour quelle colle avec ma bdd et j'ai obtenu ce que je voulais.

A+ benjamin.