SQL Select Count
The SQL COUNT() is a function that returns the number of records of the table in the output. This function is used with the SQL SELECT statement.
Let’s take a simple example: If you have a record of the voters in the selected area and want to count the number of voters, then it is very difficult to do it manually, but you can do it easily by using SQL SELECT COUNT query.
In the syntax, we have to specify the column’s name after the COUNT keyword and the name of the table on which the Count function is to be executed.
We have a table called Employee with four columns:
Suppose, you want to count the total number of bike colors from the Bike Table. For this operation, you have to write the following SQL statement:
SELECT COUNT (Bike_Cost) AS TotalBikeColor FROM Bikes ;
This query will show the following output:
The output of this query is four because two values of the Bike_Cost column are NULL and, these two NULL values are excluded from the count function. That’s why this query shows four instead of 6 in the output.
We have an Employee_details table with four columns:
Suppose, you want to count the total number of bike colors from the Bike Table. For this operation, you have to write the following SQL statement:
SELECT COUNT (Emp_City) AS TotalCity FROM Employee_details ;
This query will show the following output on the screen:
The output of this query is two because the three values of the Emp_City column are NULL. And, these three NULL values are excluded from the count function. That’s why this query shows two instead of 5 in the output.
We have an Employee_details table with four columns:
Suppose, you want to count the total values of the Emp_City column of the above Employee_details table. For this query, you have to write the following statement in Structured Query Language:
SELECT COUNT (Emp_City) AS TotalCity FROM Employee_details ;
This query will show the following output on the screen:
The output of this query is two because the three values of the Emp_City column are NULL. And, these three NULL values are excluded from the count function. That’s why this query shows two instead of 5 in the output.
NOTE: Practice below practice questions on MSSQL SERVER, it will not execute on site editor.
1.Write sql query to get all the record count from the Sales.Customer table using ADVENTURE2019 database
2.Write a query to get all the records from the person id column in Sales.Customer table using ADVENTURE2019 database
Hint: Null values in the column are not counted
3.Write a query to get all the record of card type from sales.CreditCard table, no value should be repeated
Hint: Use distinct with count
4.Write sql query to get all the record count of the title column from Person.Person table.
5.write sql query to get all the record count from middle name column of Person.Person table
Note: All queries are executed in the SQL Server using the ADVENTURE2019 database.
6. Formulate a SQL query to retrieve the total number of records present in the Sales.SalesPerson table.
7. Write an SQL query to determine the count of distinct country region codes present in the ‘SalesTerritory’ table.
8. Write an SQL query to determine the count of distinct currency codes present in the ‘CurrencyRate’ table.
9. Write an SQL query to calculate the count of email addresses that were last modified before December 22, 2008, grouped by the modification date, but only include those modification dates where the count of email addresses is greater than 1.
10. Write an SQL query to calculate the count of sales orders where the CreditCardID is greater than 10000.
YouTube Reference :
1) SQL Select Count in Hindi/Urdu
2) SQL Select Count in English
The SQL COUNT function is used to count the number of rows in a table that match a specified condition. It’s helpful for quickly calculating totals.
The basic syntax is:
sql
Copy code
SELECT COUNT(column_name) FROM table_name WHERE condition;
This counts non-null values in the specified column that meet the condition.
Advanced techniques include:
- Using COUNT with GROUP BY to calculate totals for each group.
- Combining COUNT with CASE to count conditional matches.
- Joining tables to count rows across multiple datasets.
You can apply multiple conditions using the WHERE clause. For example:
sql
Copy code
SELECT COUNT(*) FROM table_name WHERE condition1 AND condition2;
- Using COUNT(column_name) when the column contains null values.
- Forgetting to group data when using GROUP BY.
- Applying COUNT on inappropriate or overly complex conditions.
Yes, our SQL COUNT Tutorial offers a step-by-step guide for beginners and advanced users.
Yes, you can combine COUNT with other functions like SUM, AVG, and MAX to perform more complex calculations.
- COUNT(*) counts all rows, including those with null values.
- COUNT(column_name) counts only non-null values in the specified column.
Yes, COUNT can be used in subqueries to calculate totals for specific subsets of data. For example:
sql
Copy code
SELECT (SELECT COUNT(*) FROM table_name WHERE condition) AS count_value;
- Verify table and column names.
- Ensure conditions in the WHERE clause are correct.
- Use EXPLAIN or execution plans to analyze query performance.
