SQL SELECT statement with COUNT() function

What is SQL SELECT statement?

SQL SELECT statement helps us select and display the data values from the particular table of the database.

Syntax:

SELECT columns 
FROM Table-name;

Example:


SELECT * statement helps select all the data values from the provided table.

Output:

id	Cost	city
1	100	Pune
2	100	Satara
3	65	Pune
4	97	Mumbai
5	12	USA

What is SQL COUNT() function?

SQL COUNT() function counts the total number of rows present in the database.

Syntax:

Example:


SELECT Count(City)
from Info;


In this example, we have displayed the count of all the data rows under the column – ‘city’ of table – ‘Info’.

Output:

Variations of SQL SELECT statement with COUNT() function

You can use the SQL SELECT statement with the COUNT() function to select and display the count of rows in a table of a database.

Along with this, we can club SQL SELECT statement with COUNT() function in various different ways.

Having understood the working of SQL SELECT COUNT(), let us now understand different variations associated with the same through examples.

Examples of SQL SELECT with COUNT() function

To display the variations in SQL SELECT COUNT(), we have used SQL CREATE query to create a Table and SQL INSERT query to input data to the database.

We will be using the below table and its data in the further examples.

create table Info(id integer, Cost integer, city varchar(200));
insert into Info(id, Cost,city) values(1, 100,"Pune");
insert into Info(id, Cost,city) values(2, 100, "Satara");
insert into Info(id, Cost,city) values(3, 65,"Pune");
insert into Info(id, Cost,city) values(4, 97,"Mumbai");
insert into Info(id, Cost,city) values(5, 12,"USA");
select * from Info;

Output:

id	Cost	city
1	100	Pune
2	100	Satara
3	65	Pune
4	97	Mumbai
5	12	USA

1. SQL SELECT COUNT with WHERE clause

SQL SELECT COUNT() can be clubbed with SQL WHERE clause.

Using the WHERE clause, we have access to restrict the data to be fed to the COUNT() function and SELECT statement through a condition.

Example:

SELECT COUNT(city)
FROM Info
WHERE Cost>50;

Output:

2. SQL SELECT COUNT(*) function

SQL SELECT statement can be used along with COUNT(*) function to count and display the data values.

The COUNT(*) function represents the count of all rows present in the table (including the NULL and NON-NULL values).

Example:

SELECT COUNT(*)
FROM Info;

Output:

3. SQL SELECT COUNT with DISTINCT clause

The DISTINCT clause helps exclude the redundant data and displays only the unique values from the selected column.

SQL SELECT COUNT() function can be used along with DISTINCT clause to count and display the number of rows representing unique(non-repeated) values.

Example:

SELECT COUNT(DISTINCT Cost)
FROM Info;

Output:

4. SQL SELECT COUNT with HAVING and GROUP BY clause

SQL SELECT COUNT() function can be clubbed with GROUP BY and HAVING clause to add conditions before the selection of data as well as grouping of data rows by a particular column value.

Example:

SELECT city, COUNT(Cost)
FROM Info
GROUP BY city
HAVING COUNT(Cost)>1;

Output:

Conclusion

By this, we have come to an end of this topic. Please feel free to comment below in case you come across any doubt.

Create a Free Account

Register now and get access to our Cloud Services.

Posts you might be interested in:

centron Managed Cloud Hosting in Deutschland

SQL BETWEEN Operator Guide

Guide, MySQL
SQL BETWEEN Operator Guide The SQL BETWEEN operator is used along with WHERE clause for providing a range of values. The values can be the numeric value, text value, and…
centron Managed Cloud Hosting in Deutschland

SQL Commit and Rollback Guide

MySQL
SQL Commit and Rollback Guide The most important aspect of a database is the ability to store data and the ability to manipulate data. COMMIT and ROLLBACK are two such…
centron Managed Cloud Hosting in Deutschland

SQL Create Table Guide

MySQL
SQL Create Table Guide When we have to store data in relational databases, the first part is to create the database. Next step is to create a table in the…