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 ) )