Group By¶

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 Vinyl's Business Layer.

Step 1: Create a Subquery¶

To leverage GROUP BY in Vinyl, 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 ) )