-
Title
Cannot perform a SUM Aggregate Function on a Calculated column in Query Builder - Aggregate Funct -
Description
I cannot perform a SUM Aggregate Function on a Calculated column or field in Query Builder. The Aggregate Function drop down arrow or list is grayed-out.
-
Cause
You cannot perform another calculation on a column that is a result of another calculation (a Calculated column). A Calculated column is not originally part of your table. It is a column that was added by you through the Query Builder for the purpose of computing two columns in the table i.e. Credit column minus Debit column. This is the reason why the Aggregate Function drop-down list is grayed-out for that Calculated column .
-
Resolution
WORKAROUND:
Create a View that includes the Calculated column.
1. Create a Criteria in the Query Builder and make sure the columns you want as well as your Calculated column are all included. Don't set any additional Group-By or Aggregated Function yet to make it simple. Perform a Run Query, and make sure it runs successfully.
2. Now click on the Generated Query Tab and Copy all of the code. Click on the View Tab on the left panel (right next to Table tab), and then click on the "Create a View" Icon.
3. In the Create View Window that will open, give your View a new Name, and then perform a Paste function to paste the code you copied earlier. Click on the Show Script button and then click on the Execute button. Refresh your View Tab on the left panel to see the View you just created.
4. Now open a new Query Builder window, and drag the View in the modeler / modeling area. You will notice that the Calculated column is now in there, and you will now be able to perform an Aggregate Function on that Calculated column as well as a Group-By functions i.e. Sum of the Difference of the Debit and Credit columns of all the records Grouped-By account numbers.