w3resource

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:

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 : 

SELECT working_area, COUNT(*) 
FROM agents 
GROUP BY working_area;

Sample table : agents


Relational Algebra Expression:

Relational Algebra Expression: SQL COUNT() with GROUP by.

Relational Algebra Tree:

Relational Algebra Tree: SQL COUNT() with GROUP by.

Output

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

Pictorial Presentation:

SQL COUNT with GROUP BY

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: 

SELECT working_area, COUNT(*) 
FROM agents 
GROUP BY working_area 
ORDER BY 2 ;

Relational Algebra Expression:

Relational Algebra Expression: SQL COUNT() with group by and order by .

Relational Algebra Tree:

Relational Algebra Tree: SQL COUNT() with group by and order by .

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 : 

SELECT working_area, COUNT(*) 
FROM agents 
GROUP BY working_area 
ORDER BY 2 DESC;

Relational Algebra Expression:

Relational Algebra Expression: SQL COUNT() group by and order by in descending .

Relational Algebra Tree:

Relational Algebra Tree: SQL COUNT() group by and order by in descending .

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



Follow us on Facebook and Twitter for latest update.

SQL: Tips of the Day

Difference between natural join and inner join

One significant difference between INNER JOIN and NATURAL JOIN is the number of columns returned-

Consider:

TableA                           TableB
+------------+----------+        +--------------------+    
|Column1     | Column2  |        |Column1  |  Column3 |
+-----------------------+        +--------------------+
| 1          |  2       |        | 1       |   3      |
+------------+----------+        +---------+----------+

The INNER JOIN of TableA and TableB on Column1 will return

SELECT * FROM TableA AS a INNER JOIN TableB AS b USING (Column1);
SELECT * FROM TableA AS a INNER JOIN TableB AS b ON a.Column1 = b.Column1;

+------------+-----------+---------------------+    
| a.Column1  | a.Column2 | b.Column1| b.Column3|
+------------------------+---------------------+
| 1          |  2        | 1        |   3      |
+------------+-----------+----------+----------+

The NATURAL JOIN of TableA and TableB on Column1 will return:

SELECT * FROM TableA NATURAL JOIN TableB
+------------+----------+----------+    
|Column1     | Column2  | Column3  |
+-----------------------+----------+
| 1          |  2       |   3      |
+------------+----------+----------+

Ref: https://bit.ly/3AG5CId

 

We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook