SQL COUNT() with GROUP by - w3resource (2024)

Last update on December 06 2023 12:50:06 (UTC/GMT +8 hours)

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:

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 valueSELECT working_area, COUNT(*)-- From the 'agents' tableFROM agents-- Grouping the results by the 'working_area' columnGROUP BY working_area;

Sample table : agents

Relational Algebra Expression:

SQL COUNT() with GROUP by - w3resource (1)

Relational Algebra Tree:

SQL COUNT() with GROUP by - w3resource (2)

Output

WORKING_AREA COUNT(*)----------------------------------- ----------San Jose 1Torento 1London 2Hampshair 1New York 1Brisban 1Bangalore 3Chennai 1Mumbai 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:

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 valueSELECT working_area, COUNT(*)-- From the 'agents' tableFROM agents-- Grouping the results by the 'working_area' columnGROUP BY working_area-- Sorting the results by the second column (COUNT(*)) in descending orderORDER BY 2 DESC;

Relational Algebra Expression:

SQL COUNT() with GROUP by - w3resource (6)

Relational Algebra Tree:

SQL COUNT() with GROUP by - w3resource (7)

Output :

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

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

As a seasoned database expert, I bring to the table a wealth of knowledge and hands-on experience in relational databases, SQL, and query optimization. My expertise extends to understanding the nuances of data manipulation, aggregation functions, and the intricacies of using GROUP BY and ORDER BY clauses efficiently.

Now, let's delve into the concepts discussed in the provided article:

COUNT() Function with GROUP BY:
- Purpose: The COUNT() function, when used in conjunction with GROUP BY, serves the purpose of summarizing data under various groupings.
- Example:
```
SELECT working_area, COUNT(*)
FROM agents
GROUP BY working_area;
```
- Explanation: This query retrieves the 'working_area' column and counts the number of occurrences for each distinct value, grouping the results by the 'working_area' column.
ORDER BY Clause:
- Purpose: The ORDER BY clause is utilized to arrange the result set in ascending or descending order based on one or more columns.
- Example:
```
SELECT working_area, COUNT(*)
FROM agents
GROUP BY working_area
ORDER BY 2;
```
- Explanation: This query not only groups the results by 'working_area' but also sorts the results by the second column (COUNT(*)) in ascending order.
ORDER BY in Descending Order:
- Example:
```
SELECT working_area, COUNT(*)
FROM agents
GROUP BY working_area
ORDER BY 2 DESC;
```
- Explanation: Similar to the previous example, but in this case, the results are sorted in descending order by the second column (COUNT(*)).
Relational Algebra Expressions and Trees:
- The article includes representations of relational algebra expressions and trees corresponding to the SQL queries. These demonstrate the logical operations and relationships involved in processing the data.
Visual Presentation: SQL COUNT( ) with GROUP BY and ORDER BY:
- The article provides a visual presentation of the output, showcasing the 'working_area' and the corresponding counts, both in ascending and descending order.

In summary, the article covers the essential concepts of using COUNT() with GROUP BY, ORDER BY clauses, and their combinations to analyze and present data effectively in a relational database context. The inclusion of relational algebra expressions and visual presentations enhances the understanding of the underlying principles.