英文:
How to sum up one column and then group by the summed up column
问题
以下是翻译好的部分：
我有一个表格，其中包含以下内容
列 A
10
20
30
我想要的结果是
列 A 列 B
10 60
20 60
30 60
英文:
I have a table with the following
Col A
10
20
30
I want this result
Col A Col B
10 60
20 60
30 60
答案1
得分: 2
请使用其分析版本中的 sum 函数：
SQL> with test (cola) as
2 (select 10 from dual union all
3 select 20 from dual union all
4 select 30 from dual
5 )
6 select cola,
7 sum(cola) over () colb ---> 这个
8 from test;
COLA COLB
---------- ----------
10 60
20 60
30 60
SQL>;
英文:
Use sum in its analytic version:
SQL> with test (cola) as
2 (select 10 from dual union all
3 select 20 from dual union all
4 select 30 from dual
5 )
6 select cola,
7 sum(cola) over () colb --> this
8 from test;
COLA COLB
---------- ----------
10 60
20 60
30 60
SQL>
答案2
得分: 0
只需在分析函数上添加求和操作，即可获得所需的结果。
select colA,sum(colA) over() colB from Table;
英文:
Just add sum over analytical function you will get desired result.
select colA,sum(colA) over() colB from Table;
答案3
得分: 0
以下是翻译后的内容：
使用您的示例数据：
WITH tbl (COL_1) AS
(
SELECT 10 FROM Dual UNION ALL
SELECT 20 FROM Dual UNION ALL
SELECT 30 FROM Dual
)
... 您可以选择将总和作为子查询列：
Select COL_1, ( Select Sum(COL_1) From tbl ) "COL_2"
From tbl
Order By COL_1
... 或者您可以将您的表与总和子查询连接：
Select t1.COL_1, t2.COL_2
From tbl t1
Inner Join (Select Sum(COL_1) "COL_2" From tbl ) t2 ON(1 = 1)
Order By t1.COL_1
两者的结果都为：
COL_1 COL_2
---------- ----------
10 60
20 60
30 60
英文:
With your sample data:
WITH tbl (COL_1) AS
(
SELECT 10 FROM Dual UNION ALL
SELECT 20 FROM Dual UNION ALL
SELECT 30 FROM Dual
)
... you could select the sum as a subquery column:
Select COL_1, ( Select Sum(COL_1) From tbl ) "COL_2"
From tbl
Order By COL_1
... or you could join your table with the sum subquery
Select t1.COL_1, t2.COL_2
From tbl t1
Inner Join (Select Sum(COL_1) "COL_2" From tbl ) t2 ON(1 = 1)
Order By t1.COL_1
Both resulting as:
COL_1 COL_2
---------- ----------
10 60
20 60
30 60
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论