SQL SELECT COUNT

SQL SELECT COUNT: A Comprehensive Guide

SQL SELECT COUNT

The SQL COUNT() function is a powerful and widely used feature to retrieve the number of records in a table. It simplifies tasks where manually counting entries would be tedious. Whether you’re working with a database of customers, products, or employees, COUNT() helps fetch accurate results efficiently.

For example, consider you have a table of registered voters in a city and need to count the total voters. Instead of manual counting, you can use SQL’s COUNT() function to achieve this instantly.

Complete Advance AI topics:- CLICK HERE
Complete Python Course with Advance topics:-Click here

Syntax of SQL COUNT Function

SELECT COUNT(column_name) FROM table_name;  

In the syntax above:

  • Replace column_name with the name of the column you want to count.
  • Replace table_name with the name of your table.

Examples of SQL COUNT Function

Example 1: Counting Non-NULL Values in a Column

Consider a table named Vehicles with the following data:

Vehicle_Name Vehicle_Type Price
Tesla Model 3 Electric 45,000
Honda Civic Sedan NULL
Ford Mustang Sports 60,000
Toyota Corolla Sedan 25,000
Nissan Leaf Electric NULL
BMW X5 SUV 80,000

To count the total non-NULL Price values:

SELECT COUNT(Price) AS TotalPrices FROM Vehicles;  

Output:

TotalPrices
4

Here, COUNT(Price) excludes the NULL values, so only four rows are counted.

Example 2: Counting Total Entries in a Table

Using the same Vehicles table, if you want to count all entries, regardless of whether Price is NULL or not:

SELECT COUNT(*) AS TotalVehicles FROM Vehicles;  

Output:

TotalVehicles
6

The COUNT(*) function includes both NULL and non-NULL values.

SQL COUNT with the WHERE Clause

You can combine COUNT() with the WHERE clause to count rows meeting specific criteria.

Example 1: Counting Rows Based on Condition

From the Vehicles table, to count the number of electric vehicles:

SELECT COUNT(Vehicle_Name) AS ElectricVehicles FROM Vehicles WHERE Vehicle_Type = 'Electric';  

Output:

ElectricVehicles
2

Example 2: Counting Based on Another Condition

Consider another table EmployeeRecords:

Emp_ID Emp_Name Salary City
1001 Priya 60,000 Mumbai
1002 Amit 50,000 Delhi
1003 Ravi 70,000 Mumbai
1004 Sneha 40,000 Bangalore
1005 Arjun 60,000 Mumbai

To count employees based in Mumbai:

SELECT COUNT(Emp_Name) AS EmployeesInMumbai FROM EmployeeRecords WHERE City = 'Mumbai';  

Output:

EmployeesInMumbai
3

SQL COUNT with DISTINCT

To count only unique values, use the DISTINCT keyword with COUNT().

Example: Counting Unique Cities

From the EmployeeRecords table, count the unique cities employees belong to:

SELECT COUNT(DISTINCT City) AS UniqueCities FROM EmployeeRecords;  

Output:

UniqueCities
3

The three unique cities are Mumbai, Delhi, and Bangalore.

Download New Real Time Projects :-Click here

Conclusion

The SQL COUNT() function is a robust tool for handling data efficiently, offering the ability to count specific or all entries in a table. Its combination with clauses like WHERE and keywords like DISTINCT further enhances its capabilities.

For more professional database management tips, stay connected with UpdateGadh!


sql select count group by
sql select count(distinct)
select count(*) from table
count number of rows in sql query result
sql count with condition
count(1) in sql
sql count if
sql average group by
sql
sql join
sql select count row
sql select count example
sql select count oracle
sql select count w3schools

Post Comment