SUM et GROUP BY

Marsh Posté le 09-02-2023 à 10:47:07

Bonjour à tous,
J'ai cette requête :

Code :

SELECT LicenseServer.Name AS LicenseServer, FeatureName.Name AS FeatureName, FeatureType.Name AS FeatureType, aggregation_HourlyFeatureUsage.hour, aggregation_HourlyFeatureUsage.total, aggregation_HourlyFeatureUsageData.maxused FROM Feature, FeatureName, LicenseServer, aggregation_HourlyFeatureUsage, FeatureType, aggregation_HourlyFeatureUsageData WHERE FeatureName.Name = "matlab" AND LicenseServer.Name = "MATLAB : 1750@TLLICP01; 1750@TLLICP02; 1750@TLLICP03" AND Feature.FeatureNameId = FeatureName.id AND FeatureName.LicenseServerid = LicenseServer.Id AND aggregation_HourlyFeatureUsage.FeatureId = Feature.id AND FeatureType.id = FeatureName.FeatureTypeId AND aggregation_HourlyFeatureUsage.id = aggregation_HourlyFeatureUsageData.Id AND hour = "2023-01-16 18:00:00"

qui m'affiche ce résultat :

ServerName;FeatureName;TypeFeature;date;nb_lic_max;nb_lic_used
"MATLAB : 1750@TLLICP01; 1750@TLLICP02; 1750@TLLICP03";matlab;FLOATING;2023-01-16 18:00:00;49;18
"MATLAB : 1750@TLLICP01; 1750@TLLICP02; 1750@TLLICP03";matlab;NODELOCKED;2023-01-16 18:00:00;200;21
"MATLAB : 1750@TLLICP01; 1750@TLLICP02; 1750@TLLICP03";matlab;NODELOCKED;2023-01-16 18:00:00;10;1
"MATLAB : 1750@TLLICP01; 1750@TLLICP02; 1750@TLLICP03";matlab;NODELOCKED;2023-01-16 18:00:00;49;7
"MATLAB : 1750@TLLICP01; 1750@TLLICP02; 1750@TLLICP03";matlab;NODELOCKED;2023-01-16 18:00:00;4;2

J'aimerai regrouper ce résultat en deux lignes uniquement, qui ressembleraient à ça :

ServerName;FeatureName;TypeFeature;date;SUM(nb_lic_max);SUM(nb_lic_used)
"MATLAB : 1750@TLLICP01; 1750@TLLICP02; 1750@TLLICP03";matlab;FLOATING;2023-01-16 18:00:00;49;18
"MATLAB : 1750@TLLICP01; 1750@TLLICP02; 1750@TLLICP03";matlab;NODELOCKED;2023-01-16 18:00:00;263;31

En gros, faire la somme des valeurs des deux dernières colonnes selon que le type de feature soit "FLOATING" ou "NODELOCKED"

Est-ce qu'une âme charitable voudrait bien m'aider SVP ?

---------------
Mon fil vente : http://forum.hardware.fr/hfr/Achat [...] 5809_1.htm

Reply

Marsh Posté le 09-02-2023 à 10:47:07

Reply

Marsh Posté le 09-02-2023 à 12:58:15

Ajouter un GROUP BY FeatureType.Name à la fin de ta requête ?

https://www.google.com/search?q=group+by+sum

---------------
D3

Reply

Marsh Posté le 10-02-2023 à 13:24:59

Pour rendre ta requête plus lisible et moins longue, utilise des alias pour les noms des tables
Ex :
SELECT ls.Name AS LicenseServer, fn.Name AS FeatureName...
FROM LicenseServer ls, FeatureName fn, ...
WHERE...

---------------
Astres, outil de help-desk GPL : http://sourceforge.net/projects/astres, ICARE, gestion de conf : http://sourceforge.net/projects/icare, Outil Planeta Calandreta : https://framalibre.org/content/planeta-calandreta

Reply

SUM et GROUP BY

Sujets relatifs:

Leave a Replay