SQL COUNT() with GROUP by

COUNT() with GROUP by

The use of COUNT() function in conjunction with GROUP BY is useful for characterizing our data under various groupings. A combination of same values (on a column) will be treated as an individual group.

Example:

Sample table : agents

To get data of 'working_area' and number of agents for this 'working_area' from the 'agents' table with the following condition -

1. 'working_area' should come uniquely,

the following SQL statement can be used :

-- Selecting the 'working_area' column and counting the number of occurrences for each distinct value
SELECT working_area, COUNT(*)
-- From the 'agents' table
FROM agents
-- Grouping the results by the 'working_area' column
GROUP BY working_area;

Explanation:

• SELECT working_area, COUNT(*): This is the main part of the SQL query. It selects the 'working_area' column from the 'agents' table and counts the number of occurrences of each distinct value in the 'working_area' column using the COUNT(*) function. The result will include two columns: 'working_area' and the count of occurrences.


• FROM agents: This specifies the source of the data for the query, which is the 'agents' table. The FROM keyword is used to indicate the table from which the data will be selected. In this case, it selects data from the 'agents' table.


• GROUP BY working_area: This clause groups the result set by the 'working_area' column. The GROUP BY clause is used with aggregate functions like COUNT() to divide the rows returned from the SELECT statement into groups based on the values in one or more columns. In this case, it groups the rows based on the values in the 'working_area' column.

Relational Algebra Expression:

Relational Algebra Tree:

Output

WORKING_AREA                          COUNT(*)
----------------------------------- ----------
San Jose                                     1
Torento                                      1
London                                       2
Hampshair                                    1
New York                                     1
Brisban                                      1
Bangalore                                    3
Chennai                                      1
Mumbai                                       1

Visual Presentation:

SQL COUNT( ) with group by and order by

In this page, we are going to discuss the usage of GROUP BY and ORDER BY along with the SQL COUNT() function.

The GROUP BY makes the result set in summary rows by the value of one or more columns. Each same value on the specific column will be treated as an individual group.

The utility of ORDER BY clause is, to arrange the value of a column ascending or descending, whatever it may the column type is numeric or character. The serial number of the column in the column list in the select statement can be used to indicate which columns have to be arranged in ascending or descending order.

The default order is ascending if not any keyword or mention ASCE is mentioned. DESC is mentioned to set it in descending order.

Example:

Sample table: agents

To get data of 'working_area' and number of agents for this 'working_area' from the 'agents' table with following conditions -

1. 'working_area' should come uniquely,

2. counting for each group should come in ascending order,

the following SQL statement can be used:

-- Selecting the 'working_area' column and counting the number of occurrences for each distinct value
SELECT working_area, COUNT(*)
-- From the 'agents' table
FROM agents
-- Grouping the results by the 'working_area' column
GROUP BY working_area
-- Sorting the results by the second column (COUNT(*)) in ascending order
ORDER BY 2;

Explanation:

• SELECT working_area, COUNT(*): This is the main part of the SQL query. It selects the 'working_area' column from the 'agents' table and counts the number of occurrences of each distinct value in the 'working_area' column using the COUNT(*) function. The result will include two columns: 'working_area' and the count of occurrences.


• FROM agents: This specifies the source of the data for the query, which is the 'agents' table. The FROM keyword is used to indicate the table from which the data will be selected. In this case, it selects data from the 'agents' table.


• GROUP BY working_area: This clause groups the result set by the 'working_area' column. The GROUP BY clause is used with aggregate functions like COUNT() to divide the rows returned from the SELECT statement into groups based on the values in one or more columns. In this case, it groups the rows based on the values in the 'working_area' column.


• ORDER BY 2: This clause orders the result set by the second column. In SQL, column positions can be referred to by their ordinal position in the SELECT list. Here, ORDER BY 2 orders the result set by the count of occurrences, which is the second column in the SELECT list (after 'working_area'). This sorts the result set in ascending order based on the count of occurrences.

Relational Algebra Expression:

Relational Algebra Tree:

Output :

WORKING_AREA                          COUNT(*)
----------------------------------- ----------
San Jose                                     1
Torento                                      1
New York                                     1
Chennai                                      1
Hampshair                                    1
Mumbai                                       1
Brisban                                      1
London                                       2
Bangalore                                    3

SQL COUNT( ) group by and order by in descending

To get data of 'working_area' and number of agents for this 'working_area' from the 'agents' table with the following conditions -

1. ' working_area' should come uniquely,

2. counting for each group should come in descending order,

the following SQL statement can be used :

-- Selecting the 'working_area' column and counting the number of occurrences for each distinct value
SELECT working_area, COUNT(*)
-- From the 'agents' table
FROM agents
-- Grouping the results by the 'working_area' column
GROUP BY working_area
-- Sorting the results by the second column (COUNT(*)) in descending order
ORDER BY 2 DESC;

Explanation:

• SELECT working_area, COUNT(*): This is the main part of the SQL query. It selects the 'working_area' column from the 'agents' table and counts the number of occurrences of each distinct value in the 'working_area' column using the COUNT(*) function. The result will include two columns: 'working_area' and the count of occurrences.


• FROM agents: This specifies the source of the data for the query, which is the 'agents' table. The FROM keyword is used to indicate the table from which the data will be selected. In this case, it selects data from the 'agents' table.


• GROUP BY working_area: This clause groups the result set by the 'working_area' column. The GROUP BY clause is used with aggregate functions like COUNT() to divide the rows returned from the SELECT statement into groups based on the values in one or more columns. In this case, it groups the rows based on the values in the 'working_area' column.


• ORDER BY 2 DESC: This clause orders the result set by the second column in descending order. In SQL, column positions can be referred to by their ordinal position in the SELECT list. Here, ORDER BY 2 DESC orders the result set by the count of occurrences, which is the second column in the SELECT list (after 'working_area'). The DESC keyword specifies that the ordering should be in descending order.

Relational Algebra Expression:

Relational Algebra Tree:

Output :

WORKING_AREA                          COUNT(*)
----------------------------------- ----------
Bangalore                                    3
London                                       2
Hampshair                                    1
Mumbai                                       1
Brisban                                      1
Chennai                                      1
Torento                                      1
San Jose                                     1
New York                                     1

Previous: COUNT with Distinct
Next: COUNT Having and Group by