Bonjour à tous,
Je suis confronté à un problème de pourcentage:
La requête qou voici:
SELECT COUNT(*) AS NB_OCURRENCES, thesaurus_bis.niveau_0,
select_thesaurus_ter.REFERENCE
FROM mc_de INNER JOIN
thesaurus_bis ON mc_de.VALUE_KEYWORD =
thesaurus_bis.data INNER JOIN
thesaurus_ter ON mc_de.ID_OBJET =
thesaurus_ter.ID_OBJET
WHERE (thesaurus_ter.REFERENCE = '7')
GROUP BY thesaurus_bis.niveau_0, thesaurus_ter.REFERENCE
Cette action est irreversible, confirmez la suppression du commentaire ?
Signaler le commentaire
Veuillez sélectionner un problème
Nudité
Violence
Harcèlement
Fraude
Vente illégale
Discours haineux
Terrorisme
Autre
Fred BROUARD
SELECT thesaurus_bis.niveau_0, select_thesaurus_ter.REFERENCE FROM (SELECT COUNT(*) AS NB_OCURRENCES, thesaurus_bis.niveau_0, select_thesaurus_ter.REFERENCE, COUNT(*) OVER() AS TOTAL FROM mc_de INNER JOIN thesaurus_bis ON mc_de.VALUE_KEYWORD = thesaurus_bis.data INNER JOIN thesaurus_ter ON mc_de.ID_OBJET = thesaurus_ter.ID_OBJET WHERE thesaurus_ter.REFERENCE = '7' GROUP BY thesaurus_bis.niveau_0, thesaurus_ter.REFERENCE) AS T WHERE (CAST(NB_OCURRENCES AS FLOAT) / CAST(TOTAL AS FLOAT)) * 100 >= 15.0
A +
-- Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com Audit, conseil, expertise, formation, modélisation, tuning, optimisation Enseignant aux Arts & Métiers PACA et à L'ISEN Toulon - Var Technologies *********************** http://www.sqlspot.com *************************
altman a écrit :
Bonjour à tous, Je suis confronté à un problème de pourcentage:
La requête qou voici:
SELECT COUNT(*) AS NB_OCURRENCES, thesaurus_bis.niveau_0, select_thesaurus_ter.REFERENCE FROM mc_de INNER JOIN thesaurus_bis ON mc_de.VALUE_KEYWORD = thesaurus_bis.data INNER JOIN thesaurus_ter ON mc_de.ID_OBJET = thesaurus_ter.ID_OBJET WHERE (thesaurus_ter.REFERENCE = '7') GROUP BY thesaurus_bis.niveau_0, thesaurus_ter.REFERENCE
J'aurai besoin de n'afficher que les enregistrements dont la colonne "nb_ocurrences" é superieur à 15% de la somme totale des ocurrences.
Dans mon cas, la somme des ocurrences est égale à 18.
Pour chaque enregistrement je devrais vérifier la condition : nb_ocurrences >= 15/100 * somme_des_ocurrences
Ce qui me donnerait: 7 > = (15/100) * 18 OUI 6>= (15/100) *18 OUI 1 >= (15/100) *18 NON 2 >= (15/100) *18 NON
Il doit bien y avoir une manière de mettre tout cela dans une requete acev count et sum, mais je sèche...
Merci de vos idées! A+
SELECT thesaurus_bis.niveau_0, select_thesaurus_ter.REFERENCE
FROM (SELECT COUNT(*) AS NB_OCURRENCES, thesaurus_bis.niveau_0,
select_thesaurus_ter.REFERENCE,
COUNT(*) OVER() AS TOTAL
FROM mc_de
INNER JOIN thesaurus_bis
ON mc_de.VALUE_KEYWORD = thesaurus_bis.data
INNER JOIN thesaurus_ter
ON mc_de.ID_OBJET = thesaurus_ter.ID_OBJET
WHERE thesaurus_ter.REFERENCE = '7'
GROUP BY thesaurus_bis.niveau_0, thesaurus_ter.REFERENCE) AS T
WHERE (CAST(NB_OCURRENCES AS FLOAT) / CAST(TOTAL AS FLOAT)) * 100
>= 15.0
A +
--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
Enseignant aux Arts & Métiers PACA et à L'ISEN Toulon - Var Technologies
*********************** http://www.sqlspot.com *************************
altman a écrit :
Bonjour à tous,
Je suis confronté à un problème de pourcentage:
La requête qou voici:
SELECT COUNT(*) AS NB_OCURRENCES, thesaurus_bis.niveau_0,
select_thesaurus_ter.REFERENCE
FROM mc_de INNER JOIN
thesaurus_bis ON mc_de.VALUE_KEYWORD =
thesaurus_bis.data INNER JOIN
thesaurus_ter ON mc_de.ID_OBJET =
thesaurus_ter.ID_OBJET
WHERE (thesaurus_ter.REFERENCE = '7')
GROUP BY thesaurus_bis.niveau_0, thesaurus_ter.REFERENCE
SELECT thesaurus_bis.niveau_0, select_thesaurus_ter.REFERENCE FROM (SELECT COUNT(*) AS NB_OCURRENCES, thesaurus_bis.niveau_0, select_thesaurus_ter.REFERENCE, COUNT(*) OVER() AS TOTAL FROM mc_de INNER JOIN thesaurus_bis ON mc_de.VALUE_KEYWORD = thesaurus_bis.data INNER JOIN thesaurus_ter ON mc_de.ID_OBJET = thesaurus_ter.ID_OBJET WHERE thesaurus_ter.REFERENCE = '7' GROUP BY thesaurus_bis.niveau_0, thesaurus_ter.REFERENCE) AS T WHERE (CAST(NB_OCURRENCES AS FLOAT) / CAST(TOTAL AS FLOAT)) * 100 >= 15.0
A +
-- Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com Audit, conseil, expertise, formation, modélisation, tuning, optimisation Enseignant aux Arts & Métiers PACA et à L'ISEN Toulon - Var Technologies *********************** http://www.sqlspot.com *************************
altman a écrit :
Bonjour à tous, Je suis confronté à un problème de pourcentage:
La requête qou voici:
SELECT COUNT(*) AS NB_OCURRENCES, thesaurus_bis.niveau_0, select_thesaurus_ter.REFERENCE FROM mc_de INNER JOIN thesaurus_bis ON mc_de.VALUE_KEYWORD = thesaurus_bis.data INNER JOIN thesaurus_ter ON mc_de.ID_OBJET = thesaurus_ter.ID_OBJET WHERE (thesaurus_ter.REFERENCE = '7') GROUP BY thesaurus_bis.niveau_0, thesaurus_ter.REFERENCE
