Oracle SQL-使用select为某些行生成聚合行 [英] Oracle SQL - Generate aggregate rows for certain rows using select

问题描述

我有一个如下表.

|FILE| ID |PARENTID|SHOWCHILD|CAT1|CAT2|CAT3|TOTAL|
|F1  | A1 |  P1    |     N   | 3  | 2  | 6  | 11  |
|F2  | A2 |  P2    |     N   | 4  | 7  | 3  | 14  |
|F3  | A3 |  P1    |     N   | 3  | 1  | 1  | 5   |
|F4  | LG1|        |     Y   | 6  | 3  | 7  | 16  |
|F5  | LG2|        |     Y   | 4  | 7  | 3  | 14  |

现在，如果我想查找cat1，cat2，cat3&的总数(即)，是否可以?仅对showChild为'Y'的行进行总计，并将其添加到结果集中.

Now, Is it possible if I want to find the total (ie) aggregate of cat1, cat2, cat3 & total only for rows which has showChild as 'Y' and add that to the resultset.

|小孩| RES | RES | N | 10 | 10 | 10 | 30 |

|Tot| Res | Res | N | 10 | 10 | 10 | 30 |

预期的最终输出:

|FILE| ID |PARENTID|SHOWCHILD|CAT1|CAT2|CAT3|TOTAL|
|F1  | A1 |  P1    |     N   | 3  | 2  | 6  | 11  |
|F2  | A2 |  P2    |     N   | 4  | 7  | 3  | 14  |
|F3  | A3 |  P1    |     N   | 3  | 1  | 1  | 5   |
|F4  | LG1|        |     Y   | 6  | 3  | 7  | 16  |
|F5  | LG2|        |     Y   | 4  | 7  | 3  | 14  |
|Tot | Res|  Res   |     N   | 10 | 10 | 10 | 30  |

在这里，我仅在考虑将showchild为'Y'的行之后添加了Tot行(最后一行)，并将其添加到结果集中.

Here I have added the Tot row(last row) after considering only the rows which has showchild as 'Y' and added that to the resultset.

我正在尝试不使用UNION的解决方案

对于实现上述结果的任何帮助，我们深表感谢.

Any help on achieving the above results is highly appreciated.

谢谢.

推荐答案

一种方法是使用联合:

WITH cte AS (
    SELECT "FILE", ID, PARENTID, SHOWCHILD, CAT1, CAT2, CAT3, TOTAL, 1 AS position
    FROM yourTable
    UNION ALL
    SELECT 'Tot', 'Res', 'Res', 'N', SUM(CAT1), SUM(CAT2), SUM(CAT3), SUM(TOTAL), 2
    FROM yourTable
    WHERE SHOWCHILD = 'Y'
)

SELECT "FILE", ID, PARENTID, SHOWCHILD, CAT1, CAT2, CAT3, TOTAL
FROM cte
ORDER BY
    position,
    "FILE";

演示

这篇关于Oracle SQL-使用select为某些行生成聚合行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！