Requete Mysql calculer pourcentage par groupe

Version imprimable

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:

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

09/05/2021, 09h27
tatayo

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.

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

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 ?

10/05/2021, 08h23
tatayo

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

Tatayo.

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

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

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

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

Plus simple :

Code:

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 +

1 pièce(s) jointe(s)

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:

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é

Pièce jointe 597733

Merci pour votre aide et votre temps pour moi

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

Salut à tous.

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
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...

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:

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

13/05/2021, 14h11
Artemus24

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.

@+

7 pièce(s) jointe(s)

Re Salut,

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

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) ?

Pièce jointe 597760 Pièce jointe 597761 Pièce jointe 597762 Pièce jointe 597763 Pièce jointe 597764 Pièce jointe 597765

je te rejoins la structure des tables (en image)

Pièce jointe 597757

Merci de ton aide

@+

Salut PA_Maintenance.

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
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;

Re Salut,

Eh ben voilà ça marche :D 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:

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
@+