How do I add the paymentAmt based on the CaseId

Question

Hello Team, 
 
 I am trying to create a new view with expression rule or sql script that will include the sum(PaymentAmt) for cases where directDepositTF is 1. For example in the attached image, caseId 138465, I would like a view that sums the amount in paymentAmt column into a new view. Thanks

ajhick · Accepted Answer

For a view you could use this. 
 SELECT
 `caseId`,
 SUM(`paymentAmt`)
FROM `TABLE_NAME`
WHERE
	`directDepositTF` = 1
GROUP BY `caseId`;

ajhick · Answer

And for an expression rule you could use this. 
 a!queryEntity(
 entity: cons!DATA_STORE_ENTITY,
 query: a!query(
 aggregation: a!queryAggregation(
 aggregationcolumns: {
 a!queryAggregationColumn(
 field: "caseId",
 isgrouping: true
 ),
 a!queryAggregationColumn(
 field: "paymentAmt",
 isgrouping: false,
 aggregationfunction: "SUM"
 )
 }
 ),
 filter: a!queryFilter(
 field: "directDepositTF",
 operator: "=",
 value: true /*Or "1"??*/
 ),
 pagingInfo: a!pagingInfo(1, - 1)
 )
)

How do I add the paymentAmt based on the CaseId

Top Replies