Group By in Jitterbit App Builder

In SQL, the GROUP BY statement is used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the resultant rows by one or more columns. This article will step through how to use GROUP BY in App Builder's Business Layer.

Step 1: Create a subquery

To leverage GROUP BY in App Builder, you will first need to create a Subquery object that provides an aggregate function. The Subquery rule must also specify the columns that you want to GROUP BY. If you're familiar with SQL, you can confirm the Subquery GROUP BY logic by viewing the SQL tab available from the Rule screen in the Business Layer.

Subquery rule example

In this example, we will create a Subquery rule named Customer (Group By Subquery) that targets the Customer table. This rule will provide a COUNT() aggregate function, and include the columns we want to GROUP BY, which are CustomerID and ProductName.

Columns tab view:

SQL tab view:

SELECT
       C.ProductName AS ProductName,
       Count(1) AS Count,
       C.CustomerID AS CustomerID
 FROM
       Customer AS C
 GROUP BY
      C.ProductName ,
C.CustomerID

Step 2: Join the subquery to a business object

After you have a Subquery with an aggregate function, join the Subquery to a Business object. Once the Business Object is created, you can use it to Publish to UI Layer and expose the resultant data and information on an application page.

Business object rule example

Following the Subquery example, we will join the Customer (Group By Subquery) to the Customer table and establish a join using the common key field between the two objects, which in this scenario is CustomerID.

Tables tab view:

SQL tab view:

SELECT
       C.CustomerID AS CustomerID,
       C.ProductName AS ProductName,
       C.OrderDate AS OrderDate
 FROM
       "Customer (Group By Subquery)" AS CGBS
           INNER JOIN
       Customer AS C ON
       ( (CGBS.CustomerID = C.CustomerID ) )