The COUNT() function is an aggregate function that enables users to get the number of rows that match a particular requirement of a query.
Depending upon the user requirements the COUNT() function can have the following syntaxes:
Syntax: COUNT(*) Returns: All rows including NULL and Duplicates Syntax: COUNT(column) Returns: All rows except NULL. Syntax: COUNT(DISTINCT column) Returns: All rows without NULL and Duplicates
The COUNT() function is used with the SELECT statement.
For examples we will be using the sample database (ie, dvdrental).
In this example we will use the COUNT(*) function to get the number of transactions in the payment table using the command below:
SELECT COUNT(*) FROM payment;
In this example we will query for the distinct amounts which customers paid, using the COUNT(DISTINCT column) function as shown below:
SELECT COUNT (DISTINCT amount) FROM payment;
Here we will be using the COUNT() function to get the details of customers who have made more than 40 payments:
SELECT customer_id, COUNT (customer_id) FROM payment GROUP BY customer_id HAVING COUNT (customer_id) > 40;
Attention reader! Don’t stop learning now. Get hold of all the important DSA concepts with the DSA Self Paced Course at a student-friendly price and become industry ready.