Requete Mysql calculer pourcentage par groupe

**PA_Maintenance** · 08/05/2021, 18h14

Bonjour à tous,

Avant tout, je ne suis pas développeur ! et je dois pour mon activité pro (maintenance industrielle) faire des bilans dans des tables mysql dans Dolibarr. J'utilise Reportico pour interroger la base

je souhaite calculer le nombre d'heures et le pourcentage des ces heures par catégorie d'intervention. j'ai joint les tables, calculé le total des heures par catégories (merci à vos tutos, forums ...)
Maintenant je n'obtiens pas le bon résultat de % par catégorie. je vous remercie d'avance pour vos conseils et aides

la requête est écrite comme ceci (soyez indulgent !) en espérant avoir été clair dans mon descriptif

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT
llx_product.label as categorie, 
SUM(ROUND((llx_fichinterdet.duree/3600),2)) as tps_categorie, 
llx_commandedet.product_type as llx_commandedet_product_type, 
(ROUND((llx_fichinterdet.duree/3600),2) / (SELECT SUM(ROUND((llx_fichinterdet.duree/3600),2)) FROM llx_fichinterdet)) * 100 PctCat
 
FROM llx_fichinter LEFT JOIN llx_fichinterdet ON llx_fichinterdet.fk_fichinter=llx_fichinter.rowid LEFT JOIN llx_element_element ON llx_element_element.fk_target=llx_fichinter.rowid LEFT JOIN llx_commande ON llx_commande.rowid=llx_element_element.fk_source LEFT JOIN llx_commandedet ON llx_commandedet.fk_commande=llx_commande.rowid LEFT JOIN llx_product ON llx_product.rowid=llx_commandedet.fk_product
 
WHERE llx_product.fk_product_type=1
 
GROUP BY llx_product.label
 
ORDER BY tps_categorie DESC

Le résultat :

Categories	Tps Categorie	Pctcat
Travaux Programmés	118.00	0.002600
Ronde, Suivi et 5S	43.00	0.007800
Curratif	12.50	0.005200
Préventif - Prédictif	11.50	0.002600
Gestion Pieces Détachées	8.50	0.005200
Appel Astreinte	2.00	0.010400
Graissage et Lubrification	2.00	0.005200
Total Tps (h)	197,5

**tatayo** · 09/05/2021, 09h27

Bonjour,
Tu fais une jointure externe entre les tables llx_fichinter et llx_product, mais tu poses une restriction sur la deuxième table dans la clause WHERE.
De fait tu transformes la jointure externe en jointure interne.
Il faut déplacer la condition sur llx_product dans la jointure.

Je ne sais pas si le problème vient de là, mais ça va certainement jouer sur le résultat.

Pour avoir une réponse plus précise il faudrait nous fournir un jeu de test et les résultats attendus/obtenus.

Tatayo.

**PA_Maintenance** · 09/05/2021, 17h11

Bonjour,

Merci pour ton aide. le résultat a obtenir est le % par catégorie et celui que j'obtiens est dans le 1er message (les % ne sont pas correctement calculés)

Pour la jointure je ne sais pas trop mais je dois filtrer un champ de la table llx_product qui doit être = 1
Pour test j'ai retiré le filtre en mettant WHERE 1=1 mais le calcul du % est toujours erroné

le tableau de sortie souhaité

Categories	Tps Categorie (h)	Pctcat (%)
Travaux Programmés	118.00	59.75
Ronde, Suivi et 5S	43.00	21.77
Curratif	12.50	6.33
Préventif - Prédictif	11.50	5.82
Gestion Pieces Détachées	8.50	4.30
Appel Astreinte	2.00	1.01
Graissage et Lubrification	2.00	1.01
Total Tps (h)	197,5

Merci encore

une autre piste ?

**tatayo** · 10/05/2021, 08h23

Sans voir la deuxième requête, il sera difficile de dire ce qui ne va pas...

Tatayo.

**PA_Maintenance** · 10/05/2021, 21h16

Bonjour,

je vais exposer mon problème autrement, pouvez vous m'aider à écrire correctement la requête pour sortir les catégories d'intervention en heure et en pourcentage pour les techniciens

La table "interventions"

Date	Categories	Tps (h)	Commentaires	Technicien (1) Opérateur (2)
01/04/21	Depannage	1,5	réparé moteur	1
03/04/21	Preventif	2	remplacé contacteur	1
08/04/21	Astreinte	2	réarmé thermostat	2
08/05/21	Graissage	3	tapis et cvonvoyeurs	1
03/04/21	Automatisme	4	modif chargement tapis	1
01/03/21	Depannage	12	chaine entrainement	2
15/04/21	Preventif	3	basculeur colis	1
02/03/21	Astreinte	4	climatisation salle server	1
28/04/21	Graissage	2,5	convoyeurs decharge	2
14/03/21	Automatisme	2	chaufferie	1
30/04/21	Depannage	6	éclairage hall	1
03/04/21	Preventif	10	remplacé contacteur	1
01/03/21	Astreinte	4	chauffage salle	2
30/04/21	Graissage	20	parc véhicule	1
03/04/21	Automatisme	15	tapis evacuation	1
01/03/21	Preventif	6	fermeture automatique	1

le résultat souhaité, les heures et % pour les techniciens (champ = 1) seulement

Categorie	Tps (h)	% (h)
Astreinte	4	5,44
Automatisme	21	28.57
Depannage	7.5	10.2
Graissage	20	27.21
Preventif	21	28.57

Merci pour votre aide

**SQLpro** · 11/05/2021, 08h49

Plus simple :

Code :

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

1
2
3
4
SELECT Categorie SUM(Tps) AS Tps_CATEGORIE, 
       100.0 * SUM(Tps) OVER(PARTITION BY Categorie) / SUM(SUM(Tps)) OVER() AS PERCENT
FROM   ...
GROUP  BY Categorie

A +

**PA_Maintenance** · 12/05/2021, 22h49

Bonjour,

c'est mieux mais mon calcul est pas juste en %. C'est probablement dans l'adaptation avec mes tables, j'avais donné à titre d'exemple les tableaux mais je dois faire plus de jointure pour récupérer les différentes données

le code que j'ai réecrit

Code :

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

1
2
3
4
5
6
7
8
9
10
 
SELECT llx_product.label, SUM(llx_fichinterdet.duree/3600) AS tps_categorie,
       100.0 * SUM(llx_fichinterdet.duree/3600) OVER(PARTITION BY llx_product.label) / SUM(SUM(llx_fichinterdet.duree/3600)) OVER () AS PERCENT
 
FROM llx_fichinter LEFT JOIN llx_fichinterdet ON llx_fichinterdet.fk_fichinter=llx_fichinter.rowid LEFT JOIN llx_element_element ON llx_element_element.fk_target=llx_fichinter.rowid LEFT JOIN llx_commande ON llx_commande.rowid=llx_element_element.fk_source LEFT JOIN llx_commandedet ON llx_commandedet.fk_commande=llx_commande.rowid LEFT JOIN llx_product ON llx_product.rowid=llx_commandedet.fk_product
 
 
WHERE llx_product.fk_product_type=1
 
GROUP BY llx_product.label

Le résultat :

Label	Tps Categorie	Percent
Curratif	2	4,1666
Gestion Des Pieces Détachées	6.5	6.2500
Préventif - Prédictif	3	4.1666
Ronde, Suivi et 5S	5	6.2500
Travaux Programmés, Modification, Amélioration	7.5	12.500

ci joint une capture écran qui explique comment (et pourquoi) les tables sont liées avec les champs utilisés pour le calcul groupé

Nom : TablesRequeteSql_mai21.png
Affichages : 534
Taille : 97,8 Ko

Nom : TablesRequeteSql_mai21.png
Affichages : 534
Taille : 97,8 Ko

Merci pour votre aide et votre temps pour moi

**Artemus24** · 13/05/2021, 10h30

Salut à tous.

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
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
--------------
START TRANSACTION
--------------
 
--------------
set session collation_connection = "latin1_general_ci"
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
( `id`            integer unsigned  NOT NULL auto_increment primary key,
  `date`          date              NOT NULL,
  `categorie`     varchar(255)      NOT NULL,
  `tps`           decimal(10,2)     NOT NULL,
  `commentaire`   varchar(255)      NOT NULL,
  `to`            smallint unsigned NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `test` (`date`,`categorie`,`tps`,`commentaire`,`to`) VALUES
  ('2021-04-01','Depannage',    1.5, 'réparé moteur',              1),
  ('2021-04-03','Preventif',    2.0, 'remplacé contacteur',        1),
  ('2021-04-08','Astreinte',    2.0, 'réarmé thermostat',          2),
  ('2021-05-08','Graissage',    3.0, 'tapis et cvonvoyeurs',       2),
  ('2021-04-03','Automatisme',  4.0, 'modif chargement tapis',     1),
  ('2021-03-01','Depannage',   12.0, 'chaine entrainement',        2),
  ('2021-04-15','Preventif',    3.0, 'basculeur colis',            1),
  ('2021-03-02','Astreinte',    4.0, 'climatisation salle server', 1),
  ('2021-04-28','Graissage',    2.5, 'convoyeurs decharge',        2),
  ('2021-03-14','Automatisme',  2.0, 'chaufferie',                 1),
  ('2021-04-30','Depannage',    6.0, 'éclairage hall',             1),
  ('2021-04-03','Preventif',   10.0, 'remplacé contacteur',        1),
  ('2021-03-01','Astreinte',    4.0, 'chauffage salle',            2),
  ('2021-04-30','Graissage',   20.0, 'parc véhicule',              1),
  ('2021-04-03','Automatisme', 15.0, 'tapis evacuation',           1),
  ('2021-03-01','Preventif',    6.0, 'fermeture automatique',      1)
--------------
 
--------------
select * from `test`
--------------
 
+----+------------+-------------+-------+----------------------------+----+
| id | date       | categorie   | tps   | commentaire                | to |
+----+------------+-------------+-------+----------------------------+----+
|  1 | 2021-04-01 | Depannage   |  1.50 | réparé moteur              |  1 |
|  2 | 2021-04-03 | Preventif   |  2.00 | remplacé contacteur        |  1 |
|  3 | 2021-04-08 | Astreinte   |  2.00 | réarmé thermostat          |  2 |
|  4 | 2021-05-08 | Graissage   |  3.00 | tapis et cvonvoyeurs       |  2 |
|  5 | 2021-04-03 | Automatisme |  4.00 | modif chargement tapis     |  1 |
|  6 | 2021-03-01 | Depannage   | 12.00 | chaine entrainement        |  2 |
|  7 | 2021-04-15 | Preventif   |  3.00 | basculeur colis            |  1 |
|  8 | 2021-03-02 | Astreinte   |  4.00 | climatisation salle server |  1 |
|  9 | 2021-04-28 | Graissage   |  2.50 | convoyeurs decharge        |  2 |
| 10 | 2021-03-14 | Automatisme |  2.00 | chaufferie                 |  1 |
| 11 | 2021-04-30 | Depannage   |  6.00 | éclairage hall             |  1 |
| 12 | 2021-04-03 | Preventif   | 10.00 | remplacé contacteur        |  1 |
| 13 | 2021-03-01 | Astreinte   |  4.00 | chauffage salle            |  2 |
| 14 | 2021-04-30 | Graissage   | 20.00 | parc véhicule              |  1 |
| 15 | 2021-04-03 | Automatisme | 15.00 | tapis evacuation           |  1 |
| 16 | 2021-03-01 | Preventif   |  6.00 | fermeture automatique      |  1 |
+----+------------+-------------+-------+----------------------------+----+
--------------
SELECT  *,
        cast(100.0 * `tps` / SUM(`tps`) OVER() AS decimal(10,2)) AS PERCENT
  FROM  (   SELECT   `categorie`,
                     SUM(`tps`) as tps
              FROM `test`
             WHERE `to`=1
          GROUP BY `categorie`
          ORDER BY `categorie`
        ) as x
--------------
 
+-------------+-------+---------+
| categorie   | tps   | PERCENT |
+-------------+-------+---------+
| Astreinte   |  4.00 |    5.44 |
| Automatisme | 21.00 |   28.57 |
| Depannage   |  7.50 |   10.20 |
| Graissage   | 20.00 |   27.21 |
| Preventif   | 21.00 |   28.57 |
+-------------+-------+---------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

@+

**PA_Maintenance** · 13/05/2021, 11h24

Bonjour,

Super merci mais j'ai du mal à l'adapter à la contrainte de mes tables !!! (bah je maîtrise pas le sql ...)

j'ai ajouté un screenshot des tables avec leurs liaisons, ça peut aider pour l'écriture de la requête ?

Pour le moment j'ai çà : (le calcul % est pas juste)

Code :

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

1
2
3
4
5
6
7
8
9
10
 
SELECT llx_product.label, SUM(llx_fichinterdet.duree/3600) AS tps_categorie,
       100.0 * SUM(llx_fichinterdet.duree/3600) OVER(PARTITION BY llx_product.label) / SUM(SUM(llx_fichinterdet.duree/3600)) OVER () AS PERCENT
 
FROM llx_fichinter LEFT JOIN llx_fichinterdet ON llx_fichinterdet.fk_fichinter=llx_fichinter.rowid LEFT JOIN llx_element_element ON llx_element_element.fk_target=llx_fichinter.rowid LEFT JOIN llx_commande ON llx_commande.rowid=llx_element_element.fk_source LEFT JOIN llx_commandedet ON llx_commandedet.fk_commande=llx_commande.rowid LEFT JOIN llx_product ON llx_product.rowid=llx_commandedet.fk_product
 
 
WHERE llx_product.fk_product_type=1
 
GROUP BY llx_product.label

Merci

**Artemus24** · 13/05/2021, 14h11

Salut PA_Maintenance.

Pourquoi dis-tu que que le calcul du pourcentage n'est pas juste ?
Dans ton exemple que j'ai repris, il y avait une erreur.
Celle concernant la ligne : "02/03/21 Astreinte 4 climatisation salle server 1" où j'ai mis "2" au lieu de "1".
Pourquoi ? Afin d'avoir la même sommation sur la colonne "tps".

Reprends mon exemple.
Tu mets dans la sous-requête, ce que tu veux extraire de tes tables, Puis fais le calcul du pourcentage.

Si tu n'obtiens pas le bon résultat alors donne un jeu d'essai complet et présentatif de ton problème.
pour le jeu d'essai, afin que je ne retape pas tout, tu me donnes un fichier sql d'exportation.
Inutile de mettre la base de données en totalité, juste un exmple.

@+

**PA_Maintenance** · 13/05/2021, 14h52

Re Salut,

je te donne la requête qui fonctionne avec le groupement des catégories avec la somme de temps

Code :

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

1
2
3
4
5
6
7
8
9
10
 
SELECT llx_product.label as categorie, SUM(ROUND((llx_fichinterdet.duree/3600),2)) as tps_categorie, llx_commandedet.product_type as llx_commandedet_product_type
 
FROM llx_fichinter LEFT JOIN llx_fichinterdet ON llx_fichinterdet.fk_fichinter=llx_fichinter.rowid LEFT JOIN llx_element_element ON llx_element_element.fk_target=llx_fichinter.rowid LEFT JOIN llx_commande ON llx_commande.rowid=llx_element_element.fk_source LEFT JOIN llx_commandedet ON llx_commandedet.fk_commande=llx_commande.rowid LEFT JOIN llx_product ON llx_product.rowid=llx_commandedet.fk_product
 
WHERE llx_product.fk_product_type=1
 
GROUP BY llx_product.label
 
ORDER BY tps_categorie DESC

je dois joindre les 6 tables pour avoir le temps correct par interventions et les grouper par catégorie enregistrées, les jointures fonctionnent bien pour moi avec cette requête

j'ai fais plusieurs essais pour ajouter ta sous requêtes mais j'ai soit des erreurs d’exécution ou des erreurs de calcul de %

Peux tu me dire comment insérer ta sous requêtes dans celle ci ?
Ou veux tu les 6 tables (extrait joint) ?

llx_product.sql llx_fichinterdet.sql llx_fichinter.sql llx_element_element.sql llx_commandedet.sql llx_commande.sql

je te rejoins la structure des tables (en image)

Nom : TablesRequeteSql_mai21.png
Affichages : 523
Taille : 97,8 Ko

Nom : TablesRequeteSql_mai21.png
Affichages : 523
Taille : 97,8 Ko

Merci de ton aide

@+

**Artemus24** · 13/05/2021, 15h26

Salut PA_Maintenance.

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
SELECT *,cast(100.0 * tps_categorie / SUM(tps_categorie) OVER() AS decimal(10,2)) AS PERCENT
 
  FROM (     SELECT  llx_product.label,
                     SUM(llx_fichinterdet.duree/3600) AS tps_categorie
 
               FROM  llx_fichinter
 
          LEFT JOIN  llx_fichinterdet
                 ON  llx_fichinterdet.fk_fichinter = llx_fichinter.rowid
 
          LEFT JOIN  llx_element_element
                 ON  llx_element_element.fk_target = llx_fichinter.rowid
 
          LEFT JOIN  llx_commande
                 ON  llx_commande.rowid            = llx_element_element.fk_source
 
          LEFT JOIN  llx_commandedet
                 ON  llx_commandedet.fk_commande   = llx_commande.rowid
 
          LEFT JOIN  llx_product
                 ON  llx_product.rowid             = llx_commandedet.fk_product
 
              WHERE  llx_product.fk_product_type   = 1
 
           GROUP BY  llx_product.label
       ) as x;

@+

**PA_Maintenance** · 13/05/2021, 16h05

Re Salut,

Eh ben voilà ça marche

un grand merci à toi Artemus24

Encore une petite frayeur, car il ne voulait pas du SELECT *,... (j'utilise reportico, j'aurai peut du le dire avant !) j'ai donc écrit le nom des titres de colonne à la place de * et ça marche

le code qui marche chez moi :

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
 
SELECT  label, tps_categorie, cast(100.0 * tps_categorie / SUM(tps_categorie) OVER() AS decimal(10,2)) AS PERCENT
 
  FROM (     SELECT  llx_product.label,
                     SUM(llx_fichinterdet.duree/3600) AS tps_categorie
 
               FROM  llx_fichinter
 
          LEFT JOIN  llx_fichinterdet
                 ON  llx_fichinterdet.fk_fichinter = llx_fichinter.rowid
 
          LEFT JOIN  llx_element_element
                 ON  llx_element_element.fk_target = llx_fichinter.rowid
 
          LEFT JOIN  llx_commande
                 ON  llx_commande.rowid            = llx_element_element.fk_source
 
          LEFT JOIN  llx_commandedet
                 ON  llx_commandedet.fk_commande   = llx_commande.rowid
 
          LEFT JOIN  llx_product
                 ON  llx_product.rowid             = llx_commandedet.fk_product
 
              WHERE  llx_product.fk_product_type   = 1
 
           GROUP BY  llx_product.label
 
            ORDER BY llx_product.label
       ) as x;

Respect, merci encore.

Cela va m'encourager à écrire d'autres requêtes, et lire et appliquer vos tutos
Bonne journée
@+

Requete Mysql calculer pourcentage par groupe [MariaDB]

Requêtes MySQL

Discussions similaires

Partager

Partager