Jump to content
  • 0

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


Breee

Question

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

Link to comment
Share on other sites

2 answers to this question

Recommended Posts

  • 0
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! 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...