How to Set Calculated Columns and Grouping Results for SQL Query

With the help of the “SQL/AI Query” type of the JetEngine plugin, you can 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 for users and their corresponding countries. You can group the selection by the column with countries, and then only one user from each country will be displayed by default. If you combine Group results with the Calculated Columns feature, you can display a list of countries along with some calculated values for all rows (for example, the country and the number of users from this country in the database).

Another example is counting the number of posts each author has published, which will be described in this tutorial.

To create a new “SQL/AI Query”, go to WordPress Dashboard > JetEngine > Query Builder and press the “Add New” button.

Give the new query a Name and choose “SQL/AI Query Query Type. 

In the From table drop-down menu, select an SQL table from which you would like to retrieve the data (“posts” in this case).

Optionally, we press the “Add new” button in the Where (query clauses) to set such options:

• Column — “post_type”;
• Compare — “Equal (=)”;
• Value — “post”.

Such adjustments specify the default WordPress “Posts” post type as the query source. Enable the Group Results toggle. In the Group By option, choose a column by which the results will be grouped (in our example, the “post_author” option).

In the Include columns drop-down menu, select what columns should be included in the query results (the “post_author” option in our case).

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

A Calculated Columns section will appear, which contains such options:

• Column — select a column by which you want to group the results (e.g., “ID”);
• Function — pick the function that will be applied to the values of the chosen column (for our case, we choose “COUNT”):
  • 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.
• Alias — set the name under which the column will appear in the results. Avoid using the name of an existing column, as this will override its original value. In our example, it can be “all_posts_by_author”.

To check the results, scroll up the page and enable the Preview results toggle.

In our case, the query shows two results (two sites’ authors who published posts), where [post_author] shows the author ID and [all_posts_by_author] shows the post number.

Finally, if the results are satisfactory, save the query by pressing the “Add/Update Query” button.

That’s all of setting calculated columns and grouping results for “SQL Query” using the JetEngine Query Builder feature for your WordPress website.