How to Set Calculated Columns and Grouping Results for SQL Query

With the help of SQL Query in the JetEngine plugin, you could create a query with database table values. The Group Results feature allows you to combine query results by duplicate values in one of the columns. For example, you have columns with users and user countries. You could group the selection by the column with countries, and then only one of the users from each country will be displayed by default. If you combine Group results with the Calculated Columns feature, you could display a list of countries and some calculated values for all rows (for example, country and number of users from this country in the database).

To create a new SQL Query, go to the admin control panel and open JetEngine > Query Builder. Press the “Add New” button.

Give the new query a Name and choose SQL Query Type. In the From table drop-down menu, you could select an SQL table from which you would like to take the data.

Enable the Group Results toggle. In the Group By option, choose a column by which the results would be grouped. For instance, posts could be grouped by authors.

Then enable the Include Calculated Columns toggle and click on the “Add New” button.

A Calculated Columns section will appear. For every column, you should choose:

• Column. Select the column by which you would like to group the results;
• Function. Pick the function that will be applied to the values of the chosen column:
  • COUNT – this function counts the values of the column;
  • MAX – it checks the values and shows the maximum one;
  • MIN –  it checks the values and shows the minimum one;
  • SUM – counts the sum of values;
  • AVG – counts the average value.

To find out how to display a query, check out this tutorial.

That is how to use Calculated Columns and Grouping Results. With these two features, you could group and calculate duplicate column values from SQL tables.