sql—获取oracle中的列总数

ukdjmx9f 于 2021-07-26 发布在 Java

关注(0)|答案(4)|浏览(386)

我正在尝试一个sql查询（在oracle中）以x列的第一个字符对数据集进行分组，并获取行的总数和百分比。为此，我使用下面的代码。

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;

输出：

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

现在我还想得到count和percentage列的总数，如下所示。

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%

sql oracle

来源：https://stackoverflow.com/questions/62262989/getting-the-totals-of-columns-in-oracle

4条答案

按热度按时间

sirbozc51#

请使用下面的查询，

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）回复(0）举报 2021-07-26

3z6pesqy2#

只是 union all 总金额的查询

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

这将添加另一行的总计数（也总是100%）。

赞(0）回复(0）举报 2021-07-26

yks3o0rb3#

我建议在子查询中计算总数，然后使用 GROUPING SETS :

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;

编辑：
根据你的评论，你陷入了困境 union all 方法：

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

赞(0）回复(0）举报 2021-07-26

nnt7mjpx4#

你可以用 ROLLUP 具体如下：

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）回复(0）举报 2021-07-26

我来回答

sql—获取oracle中的列总数

4条答案

相关问题

热门标签

最新问答