topic Re: Sum in App Development

Sum

ClareChinonye — Wed, 06 Mar 2024 13:20:29 GMT

Hi, please help.

I'm trying to sum the LTU_QTY for Each LTU_SOURCE_REF number to get an aggregate. Just like a select sum(LTU_QTY) In SQL

this is an example of my script, but i keep getting errors

Example

LIB CONNECT TO 'ABC_LIVE SFDC';

LOAD LTU_TYPE,
LTU_SOURCE_REF,
LTU_QTY;

[UDEF_VIEW_ABC_Ltu_view]:
SELECT "LTU_TYPE",
"LTU_SOURCE_REF",
"Sum(LTU_QTY)"
FROM SFCapture.dbo."UDEF_VIEW_ABC_Ltu_view" WHERE LTU_TYPE = 20 AND (LTU_SOURCE_REF LIKE '0%' OR LTU_SOURCE_REF LIKE '1%');

Re: Sum

LRuCelver — Wed, 06 Mar 2024 13:34:18 GMT

You need to assign a field name to the calculated sum.

Try this:

Sum("LTU_QTY") as "LTU_QTY"

Re: Sum

ClareChinonye — Wed, 06 Mar 2024 13:37:27 GMT

I tried, it didn't work

Re: Sum

MatheusC — Wed, 06 Mar 2024 13:39:18 GMT

@ClareChinonye
As you are using an aggregation function, try inserting the Group by clause into your script for the dimensions.

FROM SFCapture.dbo."UDEF_VIEW_ABC_Ltu_view" ONDE LTU_TYPE = 20 AND (LTU_SOURCE_REF LIKE '0%' OU LTU_SOURCE_REF LIKE '1%') Group by "LTU_TYPE",
"LTU_SOURCE_REF";

Regarts, Matheus

Re: Sum

ClareChinonye — Wed, 06 Mar 2024 13:45:20 GMT

I tried that also,still error

Re: Sum

MatheusC — Wed, 06 Mar 2024 14:14:12 GMT

Maybe it's because I didn't rename the aggregation field

[UDEF_VIEW_ABC_Ltu_view]:
SELECT
"LTU_TYPE",
"LTU_SOURCE_REF",
“Sum(LTU_QTY)” as “LTU_QTY”
FROM SFCapture.dbo."UDEF_VIEW_ABC_Ltu_view" WHERE LTU_TYPE = 20 AND (LTU_SOURCE_REF LIKE '0%' OR LTU_SOURCE_REF LIKE '1%') Group by "LTU_TYPE","LTU_SOURCE_REF";