SQL function “Group by” would be primarily used to grouping the result values. Its use may be larger, and the results may be most usefull.

What is the function “GROUP BY”

Anyone who knows the SQL language, knows function “group by”, but even so … (little theory):

The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.

Test table and data

We’ll prepare a test table on which we test our SQL queries. The table will look like this:

If we do a simple grouping query above the table, it would look like this:

In this case, we get the result of unique column value `help_id` a sum of his values:

First task: the number of records after grouping

We will do a simple query like the previous one, and now we need to find the number of rows in the result.

Use FOUND_ROWS

The first option is to use the FOUND_ROWS, which returns the number of rows from the last query. Query will look like this:

The only rule is that the second query we must call immediately after the first query. The result of both queries:

First query returns a standard result of the grouped result. In second result we can see num of lines from first query.

Solution with subSelect

If you like subselect, this solution is very simple:

In this case, we obtain as a result of only the number of rows:

At Exaplain of query we can see, that query is veri simple, but server makes two queries:

Shortest solution – use DISTINCT function

Probably the easiest solution to this problem is to use DISTINCT (though not use grouping):

At the result we can again,see only one value

If we look at EXPLAIN query, that is again very simple

The second task: the total sum after grouping

We have a result that contains the grouped values, now we need to get the total sum from values.

Using “WITH ROLLUP” modifier

The GROUP BY clause permits a WITH ROLLUP modifier that causes extra rows to be added to the summary output. These rows represent higher-level (or super-aggregate) summary operations. ROLLUP thus enables you to answer questions at multiple levels of analysis with a single query. It can be used, for example, to provide support for OLAP (Online Analytical Processing) operations.

We will show you how it works on this example:

After executing this query we can see that the result contains one more line results. This line contain sum from individual lines:

sql server automatically calculates the sum of the values if we use any afregate function. Finally, let’s explain of this query

Use “WITH ROLLUP” with HAVING

If you need to use the HAVING clause, the result will be the same as without it:

Last row with total sum is again sum from all rows. HAVING is not taken into total result.

Third task: average of the results

We have the result after grouping, and now we need to average values​​. For this we need count of lines in result and sum of values.

Solution with subquery

First solution is with subquery, which contain standart query with group, and main query contan from this value averages:

The result shows the mean value (sum of all values / count of lines ):

Again, let’s look at EXPLAIN:

Using “WITH ROLLUP”

Possibility to get results is to use the WITH ROLLUP modifier. In this case, we do not do subquery:

Compared to the previous solution contains this result subtotals of individual lines. At the last line is again our search results:

Usage is quite simple and contains more data (for someone redundant data). Explain from this query:

Fourth task: all combination of all values

A nice example of the use the group by is to get all combinations of two columns. SQL query will look like this:

The result is a combination of all values:

We are merging data by two columns. Let go to EXPLAIN:

Evaluation

At this the time of ORM is nice to occasionally play with SQL queries.