SQL SELECT statement with COUNT() function | DigitalOcean
While we believe that this content benefits our community, we have not yet thoroughly reviewed it. If you have any suggestions for improvements, please let us know by clicking the “report an issue“ button at the bottom of the tutorial.
Hey, folks! In this article, we will be focusing on SQL SELECT statement along with COUNT() function.
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
*
from
Info;
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
SQL COUNT() function counts the total number of rows present in the database.
Syntax:
COUNT
(
column
-
name)
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:
5
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.
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 e
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:
3
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:
5
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() 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:
city COUNT
(
Cost)
Pune 2
By this, we have come to an end of this topic. Please feel free to comment below in case you come across any doubt.
For more such posts related to SQL, please do visit SQL JournalDev.
