0
votes

Obtenir les totaux des colonnes dans Oracle

J'essaie une requête SQL (dans Oracle) pour regrouper un ensemble de données par le premier caractère de la colonne x, et obtenir également le nombre total de lignes et le nombre en pourcentage. Pour cela, j'ai utilisé le code ci-dessous.

V1        Count       Percentage
A            1            0.47%
B            1            0.47%
C            54           25.59%
D            66           31.28%
E            89           42.18%
Total        211          99.99%

Résultat:

V1        Count       Percentage
A            1            0.47%
B            1            0.47%
C            54           25.59%
D            66           31.28%
E            89           42.18%

Maintenant, je veux aussi obtenir les totaux des colonnes Count et Percentage comme ci-dessous.

SELECT
    V1,
    COUNT(*) as "Count",
    to_char(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 'fm990.00')|| '%' AS "Percentage"
FROM
    (
        SELECT
            CASE
                WHEN substr(x, 0, 1) BETWEEN 'A' AND 'C'       THEN
                    'A'
                WHEN substr(x, 0, 1) BETWEEN 'J' AND 'R'       THEN
                    'B'
                WHEN substr(x, 0, 1) BETWEEN 'S' AND 'Z'       THEN
                    'C'
                WHEN substr(x, 0, 1) BETWEEN '1' AND '5'       THEN
                    'D'
                WHEN substr(x, 0, 1) BETWEEN '6' AND '7'       THEN
                    'E'
                WHEN substr(x, 0, 1) BETWEEN '8' AND '9'       THEN
                    'F'
                ELSE
                    'Unknown'
            END AS V1
        FROM
            table1
    )
group by
    V1
    order by "Count", V1;

sql oracle

0 commentaires

4 Réponses :

0
votes

Veuillez utiliser la requête ci-dessous,

SELECT
V1,
COUNT(*) as "Count",
to_char(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 'fm990.00')|| '%' AS "Percentage"
FROM
(SELECT CASE  WHEN substr(x, 0, 1) BETWEEN 'A' AND 'C'       THEN 'A'
            WHEN substr(x, 0, 1) BETWEEN 'J' AND 'R'       THEN 'B'
            WHEN substr(x, 0, 1) BETWEEN 'S' AND 'Z'       THEN 'C'
            WHEN substr(x, 0, 1) BETWEEN '1' AND '5'       THEN 'D'
            WHEN substr(x, 0, 1) BETWEEN '6' AND '7'       THEN 'E'
            WHEN substr(x, 0, 1) BETWEEN '8' AND '9'       THEN 'F'
            ELSE 'Unknown'
        END AS V1
    FROM table1
) group by  V1 order by "Count", V1

union 

SELECT 'Total', sum(count), sum(val)|| '%' AS "Percentage"
(SELECT
V1,
COUNT(*) as "Count",
to_char(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 'fm990.00') as val;
FROM
(SELECT CASE  WHEN substr(x, 0, 1) BETWEEN 'A' AND 'C'       THEN 'A'
            WHEN substr(x, 0, 1) BETWEEN 'J' AND 'R'       THEN 'B'
            WHEN substr(x, 0, 1) BETWEEN 'S' AND 'Z'       THEN 'C'
            WHEN substr(x, 0, 1) BETWEEN '1' AND '5'       THEN 'D'
            WHEN substr(x, 0, 1) BETWEEN '6' AND '7'       THEN 'E'
            WHEN substr(x, 0, 1) BETWEEN '8' AND '9'       THEN 'F'
            ELSE 'Unknown'
        END AS V1
    FROM table1
) group by  V1 order by "Count", V1) a;

0 commentaires

0
votes

Il suffit de unir tout votre requête avec le montant total

your query
UNION ALL
SELECT 'TOTAL', COUNT(*), '100%'
FROM table1
GROUP BY 1;

Cela ajoute une autre ligne avec le nombre total (qui est également toujours 100%). p >

0 commentaires

0
votes

Je suggérerais de calculer le total dans la sous-requête, puis d'utiliser GROUPING SETS :

WITH t as (
      SELECT V1, COUNT(*) as "Count",
             TO_CHAR(COUNT(*) * 100.0 / MAX(total)|| '%' AS "Percentage"
      FROM (SELECT (CASE WHEN substr(x, 0, 1) BETWEEN 'A' AND 'C' THEN 'A'
                         WHEN substr(x, 0, 1) BETWEEN 'J' AND 'R' THEN 'B'
                         WHEN substr(x, 0, 1) BETWEEN 'S' AND 'Z' THEN 'C'
                         WHEN substr(x, 0, 1) BETWEEN '1' AND '5' THEN 'D'
                         WHEN substr(x, 0, 1) BETWEEN '6' AND '7' THEN 'E'
                         WHEN substr(x, 0, 1) BETWEEN '8' AND '9' THEN 'F'
                         ELSE  'Unknown'
                     END) AS V1
            FROM table1
           ) t1
      GROUP BY V1
     )
SELECT v1, "Count", "Percentage"
FROM t
UNION ALL
SELECT 'Total', SUM("Count"),
       TO_CHAR(TO_NUMBER(REPLACE("Percentage", '%', '')), 'fm990.00')|| '%' 
FROM t

MODIFIER:

En fonction de votre commentaire, vous êtes coincé avec une approche union all :

SELECT V1, COUNT(*) as "Count",
       ROUND((COUNT(*) * 100.0 / MAX(total), 2) as p
FROM (SELECT (CASE WHEN substr(x, 0, 1) BETWEEN 'A' AND 'C' THEN 'A'
                   WHEN substr(x, 0, 1) BETWEEN 'J' AND 'R' THEN 'B'
                   WHEN substr(x, 0, 1) BETWEEN 'S' AND 'Z' THEN 'C'
                   WHEN substr(x, 0, 1) BETWEEN '1' AND '5' THEN 'D'
                   WHEN substr(x, 0, 1) BETWEEN '6' AND '7' THEN 'E'
                   WHEN substr(x, 0, 1) BETWEEN '8' AND '9' THEN 'F'
                   ELSE  'Unknown'
               END) AS V1,
              COUNT(*) OVER () as total
      FROM table1
     ) t1
GROUP BY GROUPING SETS ( (V1), () )
ORDER BY "Count", V1;

2 commentaires

J'ai essayé cette approche plus tôt, mais le souci que j'ai est que ma colonne de pourcentage devrait totaliser 99,99% en raison d'erreurs d'arrondi dans les valeurs, mais cela renvoie 100%

@DAS. . . Intéressant. Habituellement, les gens veulent que le total représente toujours la réalité, et non les erreurs d'arrondi. Mais je peux voir l'utilité de ce que vous voulez.

0
votes

Vous pouvez utiliser le ROLLUP ici comme suit:

SELECT COALESCE(V1, 'Total'), CNT, PRCNT FROM
(SELECT V1, SUM(CNT) AS CNT, SUM(PRCNT) AS PRCNT
FROM (<your_existing_query>)
GROUP BY ROLLUP(V1))

0 commentaires