需要对指定的标准进行分组和求和 [英] Need to group and sum on a criteria as specified

问题描述

MS SQL SERVER -STORED PROCEDURE





需要对标准进行分组和汇总，如下图所示..



SELECT IDNO，

LOANUMBER，

SUM（AMOUNT）AS SUM_AMT，

TX_TYPE，

GROUP BY IDNO，LOANUMBER





问题是TX_TYPE字段是（DEDUCTION，INTEREST， PRINCIPAL）。





如何组织Select语句，输出如下：



1 IDNO

2贷款

3 SUM_AMT_DEDUCTION

4 SUM_AMT_INTEREST

4 SUM_AMT_PRINCIPAL < br $>










你好在存储过程中的2个字段上订购，即在ltrim上订购（rtrim（idno））+ ltrim（rtrim（loanumber））

















谢谢

MS SQL SERVER -STORED PROCEDURE


Need to group and sum on a criteria as illustrated below..

SELECT IDNO ,
LOANUMBER ,
SUM(AMOUNT) AS SUM_AMT,
TX_TYPE ,
GROUP BY IDNO, LOANUMBER


The issue is that the field TX_TYPE is (DEDUCTION,INTEREST,PRINCIPAL).


How should I organise the Select statement such the ouput will be as follows

1 IDNO
2 LOANUMBER
3 SUM_AMT_DEDUCTION
4 SUM_AMT_INTEREST
4 SUM_AMT_PRINCIPAL





How do you order on 2 fields in a stored procedure ie order on ltrim(rtrim(idno))+ltrim(rtrim(loanumber))








Thanks

推荐答案

你可能想要为每种类型使用子查询或公用表表达式，然后进行全外连接。



或使用CASE子句。

You might want to use a subquery or a Common Table Expression for each type, then do a FULL OUTER JOIN.

Or use CASE clauses.

SELECT IDNO
, LOANUMBER
, SUM(CASE TX_TYPE WHEN 'DEDUCTION' THEN AMOUNT ELSE 0 END) AS SUM_AMT_DEDUCTION
... etc
GROUP BY IDNO, LOANUMBER

这篇关于需要对指定的标准进行分组和求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！