SQL Server Articles

Submit Article
Home » Articles » SQL Server » Data Manipulation LangRSS Feeds

Grouping Rows with GROUP BY clause

Posted By: Sarita Patel     Category: SQL Server     Views: 13385

This article explains bout how to group data using GROUP BY clause in sql server with examples.

GROUP BY clause is used to divide a table into logical groups and calculate aggregate statistics for each group.

Important categories of GROUP BY clause are as below.
  • GROUP BY clause appears after the WHERE clause and before the ORDERBY clause.
  • We can group columns or derived columns.
  • Columns from the input table can appear in an aggregate query's SELECT clause only when they are also included in the GROUP BY clause.
  • Group BY expression must match the SELECT expression exactly.
  • If you specify multiple grouping columns in the GROUP BY clause to nest groups, data is summarized at the final specified group.
  • If WHERE clause is used in the query containing a GROUP BY clause, Rows are eliminate first which dose not satisfy where condition and then grouping ocuurs.
  • You can not use column alias in the GROUP BY clause but table aliases are allowed.  

Syntax of GROUP BY Clause :

SELECT columns
        FROM table
        [WHERE search_condition]
        GROUP BY grouping_columns
        [HAVING search_condition]
        [ORDER BY sort_columns]

columns and grouping columns are one or more comma separated column names.

table is a name of table that contains columns and grouping_columns.

search_condition is a valid sql expression.

sort_columns are one or more column name. of specified table.

Examples of GROUP BY Clause :

Example 1 : Use of GROUP BY clause in select clause 

SELECT OrderID, COUNT(ProductID) AS NumberOfOrders
FROM   Order_Details

OrderID   NumberOfOrders
10248         3
10249         2
10250         3
10251         3
10252         3
10253         3
10254         3
10255         4

Above example counts the number of products ordered in particular order using group by clause. OrderID is called the grouping column.

Example 2 : Difference between COUNT(expr) and COUNT(*) in a query that contains GROUP BY clause

SELECT Region, COUNT(Region) AS TotalRegion, COUNT(*) AS TotalRows
FROM    Invoices
GROUP BY Region  

Region     TotalRegion     TotalRows
AK  24  24
DF  4  4
SP  120  120
WA  51  51
NULL  0  1329

As above example illustrates that COUNT(Region) counts only non-null values and COUNT(*) counts all values including nullnulls. In the result GROUP BY recognizes the null and creates a null group for it.

Didn't find what you were looking for? Find more on Grouping Rows with GROUP BY clause Or get search suggestion and latest updates.

Sarita Patel
Sarita Patel author of Grouping Rows with GROUP BY clause is from United States.
View All Articles

Please enter your Comment

  • Comment should be atleast 30 Characters.
  • Please put code inside [Code] your code [/Code].

No Comment Found, Be the First to post comment!