Removing a column from the GROUP BY clause

By default, Information Object Designer removes GROUP BY columns automatically. If you disable automatic grouping, you must remove GROUP BY columns manually.

Removing a GROUP BY column automatically

Information Object Designer automatically removes a column from the GROUP BY clause when:

You remove the column from the SELECT clause.

For example, consider the following information object:

SELECT orderNumber, productCode, (SUM(quantityOrdered * priceEach)) AS Total

FROM OrderDetails

GROUP BY orderNumber, productCode

You remove the productCode column from the SELECT clause. Information Object Designer automatically removes productCode from the GROUP BY clause:

SELECT orderNumber, (SUM(quantityOrdered * priceEach)) AS Total

FROM OrderDetails

GROUP BY orderNumber

You manually add a column to the GROUP BY clause that does not appear in the SELECT clause and then enable automatic grouping.

For example, consider the following information object:

SELECT orderNumber, (SUM(quantityOrdered * priceEach)) AS Total

FROM OrderDetails

GROUP BY orderNumber, productCode

The productCode column appears in the GROUP BY clause but not in the SELECT clause. You enable automatic grouping. Information Object Designer automatically removes productCode from the GROUP BY clause:

SELECT orderNumber, (SUM(quantityOrdered * priceEach)) AS Total

FROM OrderDetails

GROUP BY orderNumber

Information Object Designer automatically removes the GROUP BY clause when:

You remove all aggregate columns from the SELECT clause.

For example, consider the following information object:

SELECT orderNumber, (SUM(quantityOrdered * priceEach)) AS Total

FROM OrderDetails

GROUP BY orderNumber

You remove the aggregate column SUM(quantityOrdered * priceEach) from the SELECT clause. Information Object Designer automatically removes the GROUP BY clause:

SELECT orderNumber

FROM OrderDetails

You remove all non-aggregate columns from the SELECT clause.

For example, consider the following information object:

SELECT orderNumber, (SUM(quantityOrdered * priceEach)) AS Total

FROM OrderDetails

GROUP BY orderNumber

You remove the orderNumber column from the SELECT clause. Information Object Designer automatically removes the GROUP BY clause:

SELECT (SUM(quantityOrdered * priceEach)) AS Total

FROM OrderDetails

Removing a GROUP BY column manually

If you disable automatic grouping, you must remove GROUP BY columns manually.

Copyright Actuate Corporation 2012