英文:
How to sum total amount without grouping even have duplicated number?
问题
结果20.0 VC1000110.0 VC10003----------------------------
可能显示结果如下吗？
结果10.0 VC1000110.0 VC1000110.0 VC10003
希望能帮助到您。
英文:
table_a table_bdesc amount ID vcref IDbanana 2.00 101 VC10001 101apple 3.00 101 VC10001 101orange 5.00 101 VC10003 101select sum(a.amount),b.vcref from table_a a,table_b b where a.ID=b.ID group by b.vcref;result20.0 VC1000110.0 VC10003-------------------------------------------
May I know it is possible to show result like this ?
result10.0 VC1000110.0 VC1000110.0 VC10003
Anyone help is much appreciated.
答案1
得分: 1
选择作为内部查询的总和：
select(select sum(amount) from table_a) as 结果,vcreffrom table_b
英文:
Select the sum as an inner query:
select(select sum(amount) from table_a) as result,vcreffrom table_b
答案2
得分: 1
使用相关子查询的一种选择是：
示例数据：
SQL> with2 table_a (c_desc, amount, id) as3 (select 'banana', 2, 101 from dual union all4 select 'apple' , 3, 101 from dual union all5 select 'orange', 5, 101 from dual6 ),7 table_b (vcref, id) as8 (select 'vc1001', 101 from dual union all9 select 'vc1001', 101 from dual union all10 select 'vc1003', 101 from dual11 )查询：12 select b.vcref,13 (select sum(a.amount) from table_a a where a.id = b.id) total14 from table_b b;VCREF TOTAL------ ----------vc1001 10vc1001 10vc1003 10SQL>;
英文:
One option is to use a correlated subquery:
Sample data:
SQL> with2 table_a (c_desc, amount, id) as3 (select 'banana', 2, 101 from dual union all4 select 'apple' , 3, 101 from dual union all5 select 'orange', 5, 101 from dual6 ),7 table_b (vcref, id) as8 (select 'vc1001', 101 from dual union all9 select 'vc1001', 101 from dual union all10 select 'vc1003', 101 from dual11 )
Query:
12 select b.vcref,13 (select sum(a.amount) from table_a a where a.id = b.id) total14 from table_b b;VCREF TOTAL------ ----------vc1001 10vc1001 10vc1003 10SQL>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论