w3resource logo


>Sql Count with group by

SQL count( ) with group by

rating SQL Count() with group by has average rating 7 out of 10. Total 32 users rated.

<<PreviousNext>>

Description

In this page we are discussing the usage of GROUP BY along with the SQL COUNT() function to make a group of values on a specific column. 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 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;

Output

Sql Sql Count( ) group by

Pictorial Representation

Sql Count( ) group by

SQL count( ) with group by and order by

Description

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 a 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 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 ;

Output

Sql Count( ) group by and order by

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 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;

Output

Sql Count( ) with having

Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition.

See our Model Database

Here is a new document which is a collection of questions with short and simple answers, useful for learning SQL as well as for interviews.



<<PreviousNext>>