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