SQL Select SUM and Group BY Statements for Aggregations in Calculated field

[Breee]

Hello, 

I'm using a tabular search and report for an invoicing application where I need to show aggregations of the RateAmount grouping by the RecIDL at the bottom of the RateAmount column.  I want to pass the aggregated calculated field total to the invoice using an href link to my details page. Currently the link to the details page is showing  an invoice for each RecIDL. I need the RecIDL to group on the tabular search and report with the aggregated total for each RecIDL. 

 

Here are the specifics: 

 

The Table name is 'Invoicing'. 

I have columns for 'RecIDL' and 'RateAmount'. 

For example: 

Table Name: Invoicing

(Columns):   RecIDL              RateAmount   

 (Vaules):       L1                       $100.00

                       L1                       $100.00

                       L2                        $150.00

                       L3                        $175.00     

I can aggregate totals and group by the RecIDL using the aggregate caspio field; however I need this information to pass to the actual invoice so a calculated field is required. 

 

I'd like to select the 'RecID column' and sum the 'RateAmount' and then group by the 'RecID.'

 

I've tried the following select statements. 

SELECT RecIDL, 
SUM(IsNull(RateAmount,0))
FROM Invoicing_Loads
GROUP BY RecIDL

(*Expression Error Msg:  Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. )

 

SELECT RecIDL, 
SUM (RateAmount) 
FROM Invoicing
GROUP BY RecIDL;

(*Valid Formula per Caspio verify: When html page is opened "error in formula" message received; unable to view webpage)

 

SELECT SUM(RateAmount) FROM Invoicing_Loads WHERE RecIDL = target.[@field:RecIDL] Group by RecIDL

(*Statement is grouping the RateAmount for each RecIDL. The amount is appearing numerous times on the report; the total is correct.) 

 

This is what is showing: (The total should be $200.00 for L1)

(Columns): RecIDL      RateAmount

(Vaules):       L1               $200.00

                      L1               $200.00  

                      L2               $150.00

                      L3               $175.00  

 

This is what I need to see: Total Aggregations for each RecIDL; preferably at the bottom of each RecIDL group. 

(Columns):   RecIDL              RateAmount   

 (Total L1):      L1                       $200.00

 (Total L2)       L2                        $150.00

 (Total L3)       L3                        $175.00     

 

I'm not a strong sql user so I'm not certain what the "expression" error is speaking to. Based on the knowledge that I do have I believe it should work. 

I have searched the forums but could not locate any information specific to my need. I'd be grateful for any assistance provided. As always, Thank you!
 

Bre

[Vitalikssssss]

Hello Breee,

Quote

(*Valid Formula per Caspio verify: When html page is opened "error in formula" message received; unable to view webpage)

This error might occur if you have some records with Blank entries.

Hope this helps.

[Breee]

1 minute ago, Vitalikssssss said:

Hello Breee,

This error might occur if you have some records with Blank entries.

Hope this helps.

Thanks for the response Vitalikssssss. That's what I thought as well. I placed 'null' clause in the statement but then I get a Caspio invalid formula "expression" error. 

 

SELECT RecIDL, 
SUM(IsNull(RateAmount,0))
FROM Invoicing_Loads
GROUP BY RecIDL

(*Caspio Expression Error Invalid Formula Msg:  Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. )

 

No too sure what to do at this point. Thanks again! 

[Vitalikssssss]

Hello Bree,

I am afraid that SQL function Group By is not available within Calculated/Total&Aggregation fields since this function does not return a scalar value.

Perhaps you can try to use standard grouping available for Tabular Report Datapage.  

Hope this helps.