GROUP BY

The GROUP BY clause is used in SELECT statements to group selected rows by the value of a column or other expression into a set of summary rows and calculate any aggregate functions in each group. The GROUP BY clause is always evaluated after the WHERE clause but before the HAVING clause. Therefore, the WHERE clause cannot contain aggregate functions.

The aggregate functions allow you to perform calculations on each group into single values. The most common aggregate functions are SUM, AVG, MAX, MIN and COUNT. The HAVING clause can then be used to filter the final results similar to the WHERE clause but including aggregated values. When no GROUP BY clause is specified but statement includes aggregate functions, all selected rows are grouped into one single row.

Syntax

SELECT ...
GROUP BY {col_name | expression}

The GROUP BY clause is used as follows:

  1. The GROUP BY clause consists of the GROUP BY keyword followed by a single expression. The expression can be a column reference or any function supported by the connector except for aggregate functions (which are calculated as result of the GROUP BY).

    Example: simple select that returns the number of employees for each role:

    SELECT Role, COUNT(ID) FROM Employees GROUP BY Role

    Example: using functions as GROUP BY expressions, here grouping by the year when employee was created:

    SELECT DATEFORMAT(Created, 'yyyy') as Year, COUNT(ID) FROM Employees
    GROUP BY DATEFORMAT(Created, 'yyyy')

  2. The aggregate functions, such as AVG or COUNT, can be used in the SELECT, HAVING or ORDER BY clause.

    Example: selecting the average age of employees for each role where average is at least 30 and finally sorting on age in ascending order.

    SELECT Role, AVG(Age) AS Average FROM Employees
    GROUP BY Role
    HAVING Average >= 30
    ORDER BY Average ASC

  3. For selected columns that are not part of the group by expression or aggregate functions the final results contains the value of the first row in each group. Because the ORDER BY clause is evaluated after the GROUP BY clause, there is no given sort order within each group of rows. Be careful when selecting columns, since the value of such columns can not be predicted.

    Example: the following value of Sex cannot be predicted because it can be any of M/F:

    SELECT Role, Sex, COUNT(ID) FROM Employees GROUP BY Role

Code examples

Calculate sum of orders per customer

using (var connection = new SharePointConnection(connectionString))
{
    connection.Open();
    using (var command = new SharePointCommand(@"SELECT C.Name, COUNT(O.ID) AS Count, SUM(O.Price) AS Sum FROM Customers C INNER JOIN Orders O ON C.ID = O.Customer GROUP BY C.ID ORDER BY C.Name", connection))
    {
        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine(reader["Name"].ToString().PadRight(30) + " : " + reader["Count"].ToString().PadRight(30) + " : " + reader["Sum"].ToString());
            }
        }
    }
}

Code result

Adam Sandler                   : 1                             : 500
John Smith                     : 12                            : 6000
King Carl XVI Gustaf           : 3                             : 1500
Leia Hunden                    : 4                             : 2000
Ulf Engstrand                  : 6                             : 3000

Calculate sum of orders per country

using (var connection = new SharePointConnection(connectionString))
{
    connection.Open();
    using (var command = new SharePointCommand(@"SELECT C.Country, SUM(O.Price) AS Sum FROM Customers C INNER JOIN Orders O ON C.ID = O.Customer GROUP BY C.Country ORDER BY Sum", connection))
    {
        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine(reader["Country"].ToString().PadRight(30) + " : " + " : " + reader["Sum"].ToString());
            }
        }
    }
}

Code result

Sweden                         : 6500
United Kingdom                 : 6000
USA                            : 500

Calculate sum of orders per month in year 2012

using (var connection = new SharePointConnection(connectionString))
{
    connection.Open();
    using (var command = new SharePointCommand(@"SELECT MONTH(Created) AS Month, SUM(Price) AS Sum FROM Orders WHERE YEAR(Created) = 2012 GROUP BY MONTH(Created) ORDER BY MONTH(Created)", connection))
    {
        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine(reader["Month"].ToString().PadRight(30) + " : " + " : " + reader["Sum"].ToString());
            }
        }
    }
}

Code result

1                              : 500
3                              : 1000
4                              : 2500
5                              : 1500
8                              : 1500
10                             : 1000
11                             : 3000
12                             : 2000


Skip Navigation Links.

Visa     MasterCard