Query Builder: GROUP BY and aggregate functions

Here we will discuss the use of the GROUP BY clause and the use of aggregate functions like COUNT(), MAX(), AVG() (average) etc. Using such functions in combination with criteria can be a problem for a lot of users. SQL defines a special construction (the HAVING clause) for handling criteria on those. Using this clause correctly - especially in combination with the GROUP by clause, 'common' functions and complex JOINs is 'too hairy' for many users - and many simply stayed away from that. That is a shame because these sort of queries can be very powerful - and in a single SQL statement you may be able to get a result directly that would otherwise take a lot of additional work or application code to retrieve!

With the SQLyog Query Builder it becomes very easy to handle this. The program decides which criteria will have to go into a WHERE clause and which must go into a HAVING clause. You won't have to think about different rules between 'common' and 'aggregate' functions - no matter if the column is used for sorting, filtering or used with a GROUP BY.

On the `actor_id` column we use the aggregate function COUNT() and a criteria at the same time. You see that this criteria goes into the HAVING clause and not the WHERE clause. The criteria on year(release_year) is (correctly) in the WHERE clause, however.

In this example you also see a GROUP BY clause on `substr(special_features....)`. That means that all rows having the same value for substr(special_features ....) will be GROUP'ed into a single line in the result. By using the MIN() aggregate function of the `release_year` we will ensure that the 'lowest' value for `release_year` will be displayed. As `release_year` is a DATE 'lowest' means 'first' in terms of TIME. In plain words this query will return a single line displaying the date of the first order to be delivered for every customer who has an order registered in the system.

We will study one more advanced example:

There are three things you should notice here:

First: you should note that in this example there is one column (ìtem_id) on which there simultaneously is used 'GROUP BY, a criteria, and aggregate function (COUNT()). No problem! Note that when using GROUP BY on columns where an aggregate functions is also used, SQLyog correctly does the GROUP BY on 'the column itself' and not on the 'aggregated column'.

Second: you should study the criteria entered in this example. There are quite a lot of them! First note that SQLyog 'keeps track of' which criteria shall be used with WHERE and HAVING respectively. The AND/OR rule of entering horizontally/vertically in the GRID as described before is applied to each of those two categories independently. So the two criteria like " 'sam%' " and " = 'ACADEMY DINOSAUR' " can safely be entered on the same line even if you do not want them logically 'AND'ed' if you are sure that they belong to the WHERE and HAVING clauses respectively. If you are not sure, then you may just as well enter each of them on their own separate line of the GRID. The resulting query will be the same.

Third: SQLyog Query Builder lets you specify an ALIAS (a 'nickname') for a column (or for a function/expression defined on a column). When executing the query the ALIAS will be displayed as the column header for the column in the result.

Lastly, also  note that all over this chapter of the manual we have restricted ourselves to using examples with only two tables. This was done in order not to make an example graphics unnecessarily complicated, but rather to keep them as simple as possible. The principles are the same no matter if there are used 2 or 10 tables for the query. You can of course use as many tables as you like. The structure of JOINs handled by SQLyog can be a 'chain-of-JOINs' or a 'tree-of-JOINs' and every combination of the two. It is further a restriction that only tables from the same database can be JOINED.

Note: This feature is available in SQLyog Enterprise and Ultimate.