Creating a GROUP BY clause
By default, Information Object Designer creates a GROUP BY clause automatically. If you prefer, you can create a GROUP BY clause manually.
Creating a GROUP BY clause automatically
When an information object’s SELECT clause includes an aggregate column and one or more non-aggregate columns, the non-aggregate columns must appear in the GROUP BY clause. If the non-aggregate columns do not appear in the GROUP BY clause, Information Object Designer displays an error message. For example, consider the following information object:
SELECT orderNumber, (SUM(quantityOrdered * priceEach)) AS Total
FROM OrderDetails
When you attempt to compile the information object, the error message shown in Figure 3-39 appears in the Problems view.
Figure 3-39  
To avoid this problem, Information Object Designer automatically creates a GROUP BY clause:
SELECT orderNumber, (SUM(quantityOrdered * priceEach)) AS Total
FROM OrderDetails
GROUP BY orderNumber
If more than one column appears in the GROUP BY clause, you can change the order of the columns using the up and down arrows in Group By, as shown in Figure 3-40.
Figure 3-40  
Creating a GROUP BY clause manually
If automatic grouping does not generate the desired SQL query, create the GROUP BY clause manually. Create the GROUP BY clause manually if you want to group on a column that does not appear in the SELECT clause, for example:
SELECT (SUM(quantityOrdered * priceEach)) AS Total
FROM OrderDetails
GROUP BY orderNumber

Additional Links:

Copyright Actuate Corporation 2012