Grouping (GROUP BY)

You can group the SELECT query results, based on one or several columns using the GROUP BY SQL operator. After the GROUP BY operator, you specify the column name that possibly contains duplicate names. Performing grouping, you will have just one record for every value in the specified column. Typically you may use some SQL aggregate functions to display in other column(s) sum, max, min, or count for the grouped records.

In the SELECT statement, GROUP BY clause follows the WHERE condition and precedes the ORDER BY clause.

Assume we have awards table storing the data on FIFA Golden Ball award winners, and we want to count the awards for each player in the table:

year player
2010 Lionel Messi
2011 Lionel Messi
2012 Lionel Messi
2013 Cristiano Ronaldo
2014 Cristiano Ronaldo

We want to count the number of awards for each player, and will use grouping for this purpose. In SQL, grouping can be done in the following form: 

SELECT playerCOUNT(player) FROM awards GROUP BY player;

which is equivalent to the following QueryDSL:

selectFrom(awards)
    .groupBy(awards.player)
    .list(awards.player, awards.player.count());

The result will be: 

Lionel Messi 3
Cristiano Ronaldo 2