Compter une colonne

**Hotgeart** · 07/02/2013, 12h49

Bonjour à tous

,

J'ai une table clients, projects et files.

Je cherche a sortir un tableau du style :

Nom client | Nombre de projects | Taille total de tous les fichiers des projets du client

Ma table client est sous cette forme (simplifié)

id
name

Ma table projects (simplifié)

id
name
client_id

ma table files

id
name
size
project_id

Pour le moment voici ce que j'ai :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
SELECT name, 
(SELECT COUNT(id) FROM projects WHERE clients.id = projects.client_id) as n_project
FROM clients

Ce qui me donne : Nom du client | Nombre de project
ex:
Google => 5
Nike => 2
etc...

Et donc j'aimerai avoir la taille des fichiers des projets de chaque clients
ex:

Google => 5 => 200

Soit google à 5 projets et ces projets contiennent x fichiers qui font au total 200 octets

J’imagine que c'est un truc du genre mais je sèche :/

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT id, name, slug, ckey, 
(SELECT COUNT(id) FROM projects WHERE clients.id = projects.client_id) as n_project, 
(SELECT SUM(size) FROM files WHERE ?????????? ) as c_size 
FROM clients

Merci de m'aider

. Bonne journée

**aieeeuuuuu** · 07/02/2013, 13h53

Bonjour,

Essayez ceci

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
 
SELECT
    C.name,
    COUNT(DISTINCT P.id) AS NbProjets,
    SUM(F.size) AS TailleTotale
FROM    Client C
INNER JOIN Projet P
    ON    P.client_id = C.Id
INNER JOIN Files F
    ON    F.project_id = P.id
GROUP BY C.name

**Hotgeart** · 07/02/2013, 14h11

Merci de votre réponse.

Pour les besoins de l'affichage j'ai changé les noms, mais pas votre structure:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
 
SELECT
	c.id as id, c.name as name, c.slug as slug, c.ckey as ckey,
	COUNT(DISTINCT P.id) AS n_project,
	SUM(F.size) AS ClientDisk
FROM    Clients C
INNER JOIN Projects P
	ON    P.client_id = C.Id
INNER JOIN Files F
	ON    F.project_id = P.id
GROUP BY C.name

J'ai bien la bonne size des files, mais n_project votre ancien NbProjets renvoie toujours 1 qui est le nombre de project du premier résultat.

Merci

**al1_24** · 07/02/2013, 14h35

Toutes les colonnes qui ne font pas l'objet d'une fonction de regroupement doivent être mentionnées dans la clause GROUP BY.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
SELECT
	c.id AS id, c.name AS name, c.slug AS slug, c.ckey AS ckey,
	COUNT(DISTINCT P.id) AS n_project,
	SUM(F.size) AS ClientDisk
FROM    Clients C
INNER JOIN Projects P
	ON    P.client_id = C.Id
INNER JOIN Files F
	ON    F.project_id = P.id
GROUP BY c.id AS id, c.name AS name, c.slug AS slug, c.ckey AS ckey

La majorité des SGBD, qui respectent les règles de la syntaxe SQL, auraient refusé votre requête.

**aieeeuuuuu** · 07/02/2013, 14h41

heu...

Vous avez ajouté des colonnes dans le select mais pas dans le group by...

Vos requête n'est donc pas correcte, et vous devez être sous MySQL, qui, plutôt que vous remonter une erreur, vous remonte des résultats aléatoires...

Modifiez le group by

**Hotgeart** · 07/02/2013, 14h46

Vos deux requêtes sans mes ajouts de select retourne quand même 1 en nombre de project

**CinePhil** · 07/02/2013, 14h49

Que donne cette requête ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT client_id, COUNT(*) AS n_projets
FROM Projects
GROUP BY client_id

**Hotgeart** · 07/02/2013, 14h57

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
client_id | n_projects
     1     |      1
     2     |      2

Ce qui est juste le client 1 a 1 project et le client 2 en a deux

**aieeeuuuuu** · 07/02/2013, 15h11

Envoyé par Hotgeart

Ma table projects (simplifié)

simplifiée comment ????

que donne ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
SELECT *
FROM Projetcs
WHERE client_id = 2

**al1_24** · 07/02/2013, 15h20

Et chacun des projets du client 2 a bien des lignes référencées dans Files ?

Essaye avec :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
SELECT c.id AS id, c.name AS name, c.slug AS slug, c.ckey AS ckey,
	COUNT(DISTINCT P.id) AS n_project,
	SUM(F.size) AS ClientDisk
FROM    Clients C
INNER JOIN Projects P
	ON    P.client_id = C.Id
LEFT JOIN Files F
	ON    F.project_id = P.id
GROUP BY c.id AS id, c.name AS name, c.slug AS slug, c.ckey AS ckey

**Hotgeart** · 07/02/2013, 15h30

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
CREATE TABLE IF NOT EXISTS `clients` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `logo` varchar(255) NOT NULL,
  `ckey` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;
 
 
CREATE TABLE IF NOT EXISTS `files` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `image` varchar(255) NOT NULL,
  `file` varchar(255) NOT NULL,
  `size` int(11) NOT NULL,
  `version` varchar(255) NOT NULL,
  `fdate` date NOT NULL,
  `project_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `project_id` (`project_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;
 
CREATE TABLE IF NOT EXISTS `projects` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `client_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `client_id` (`client_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

Voila l'archi de ma bdd

@al1_24 Mysql me renvoi ceci : #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 id, c.name AS name, c.slug AS slug, c.ckey AS ckey LIMIT 0, 30' at line 9

**aieeeuuuuu** · 07/02/2013, 15h32

Et chacun des projets du client 2 a bien des lignes référencées dans Files ?

Je crois qu'Al1_24 a vu juste...

@al1_24 Mysql me renvoi ceci : #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 id, c.name AS name, c.slug AS slug, c.ckey AS ckey LIMIT 0, 30' at line 9

Enlevez les AS xxx dans le GROUP BY

**Hotgeart** · 07/02/2013, 16h26

Sans les as du code de al1_24 ça marche. Cependant, si un client n'a pas de project il ne s'affiche pas.

merci pour vos réponses

**aieeeuuuuu** · 07/02/2013, 16h59

Envoyé par Hotgeart

Cependant, si un client n'a pas de project il ne s'affiche pas.

Il suffit de faire une jointure externe sur la table projet aussi...

**Hotgeart** · 08/02/2013, 10h43

Bonjour,

J'essaye encore et encore depuis hier, mais je n'y arrive pas il ne m'affiche jamais cette ligne avec 0 project

J'ai essayé ces requests et des variantes :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 c.id, c.name, c.slug, c.ckey,
	COUNT(DISTINCT P.id) AS n_project,
	SUM(F.size) AS ClientDisk
FROM    Clients C
INNER JOIN Projects P
	ON    P.client_id = C.Id
LEFT JOIN Files F
	ON    F.project_id = P.id
RIGHT OUTER JOIN Projects
	ON    C.id = projects.client_id
GROUP BY c.id, c.name, c.slug, c.ckey
 
SELECT c.id, c.name, c.slug, c.ckey,
	COUNT(DISTINCT P.id) AS n_project,
	SUM(F.size) AS ClientDisk
FROM    Clients C
INNER JOIN Projects P
	ON    P.client_id = C.Id
LEFT JOIN Files F
	ON    F.project_id = P.id
LEFT OUTER JOIN Projects
	ON    projects.client_id = C.id
GROUP BY c.id, c.name, c.slug, c.ckey
 
SELECT c.id, c.name, c.slug, c.ckey,
	COUNT(DISTINCT P.id) AS n_project,
	SUM(F.size) AS ClientDisk
FROM    Clients C
INNER JOIN Projects P
	ON    P.client_id = C.Id
LEFT JOIN Files F
	ON    F.project_id = P.id
LEFT JOIN Projects
	ON    projects.client_id = C.id
GROUP BY c.id, c.name, c.slug, c.ckey
 
 
ETC...

Avec Google, je tombe sur des forums ou on dit qu'une jointure extern doit comporter un OUTER d'autres disent le contraire bref je ne sais plus quoi penser :/ .

Merci de vos lumières

**al1_24** · 08/02/2013, 10h54

Et comme ça tout simplement, ça ne fonctionne pas ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT  c.id    AS id
    ,   c.name  AS name
    ,   c.slug  AS slug
    ,   c.ckey  AS ckey
    ,   COUNT(DISTINCT P.id)    AS n_project
    ,   SUM(F.size)             AS ClientDisk
FROM    Clients C
    LEFT JOIN Projects P
        ON  P.client_id = C.Id
    LEFT JOIN Files F
        ON  F.project_id = P.id
GROUP BY c.id
    ,   c.name
    ,   c.slug
    ,   c.ckey
;

PS : pour mieux comprendre les jointures, tu peux aller voir ici

**CinePhil** · 08/02/2013, 10h57

Pourquoi faire deux jointures sur la table des projets ?

En supposant qu'un projet ne compte qu'une seule ligne dans la table des projets, combien de projets par client ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT c.id, c.name, c.slug, c.ckey,
	COUNT(p.id) AS n_project
FROM Clients c
LEFT OUTER JOIN Projects p ON p.client_id = c.id
GROUP BY c.id, c.name, c.slug, c.ckey

Maintenant j'ajoute la jointure avec les fichiers :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
SELECT c.id, c.name, c.slug, c.ckey,
	COUNT(p.id) AS n_project
FROM Clients c
LEFT OUTER JOIN Projects p ON p.client_id = c.id
	LEFT OUTER JOIN Files f ON f.project_id = p.id
GROUP BY c.id, c.name, c.slug, c.ckey

Contrairement à ce qu'indique la fonction COUNT, comme il peut y avoir plus d'un fichier par projet, la requête ne compte pas les projets mais les fichiers.
Pour compter réellement combien il y a de projets, il faut utiliser COUNT(DISTINCT p.id)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
SELECT c.id, c.name, c.slug, c.ckey,
	COUNT(DISTINCT p.id) AS n_project
FROM Clients c
LEFT OUTER JOIN Projects p ON p.client_id = c.id
	LEFT OUTER JOIN Files f ON f.project_id = p.id
GROUP BY c.id, c.name, c.slug, c.ckey

Maintenant, je peux calculer la taille totale des fichiers du projet :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SELECT c.id, c.name, c.slug, c.ckey,
	COUNT(DISTINCT p.id) AS n_project,
	SUM(f.size) AS ClientDisk
FROM Clients c
LEFT OUTER JOIN Projects p ON p.client_id = c.id
	LEFT OUTER JOIN Files f ON f.project_id = p.id
GROUP BY c.id, c.name, c.slug, c.ckey

LEFT (ou RIGHT) JOIN ou LEFT OUTER (ou RIGHT) JOIN sont équivalents.
On peut dire que OUTER est un mot inutile. Il précise juste sémantiquement qu'il s'agit bien d'une jointure externe, par opposition à la jointure interne INNER JOIN. Le mot clé INNER est d'ailleurs, lui aussi, facultatif ; un simple JOIN est une jointure interne.

**Hotgeart** · 08/02/2013, 12h20

Merci

Compter une colonne

Langage SQL

Discussions similaires

Partager

Partager