Removing a column from the GROUP BY clause

By default, Information Object Query Builder 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 Query Builder automatically removes a column from the GROUP BY clause when you complete the following actions:

You remove the column from the SELECT clause. For example, consider the following information object query:

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 Query Builder 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 query:

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 Query Builder automatically removes productCode from the GROUP BY clause:

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

FROM OrderDetails

GROUP BY orderNumber

Information Object Query Builder automatically removes the GROUP BY clause when:

You remove all aggregate columns from the SELECT clause. For example, consider the following information object query:

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 Query Builder 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 query:

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

FROM OrderDetails

GROUP BY orderNumber

You remove the orderNumber column from the SELECT clause. Information Object Query Builder 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 manually remove columns from the GROUP BY clause.

Copyright Actuate Corporation 2012