Home / SQL Tutorial / How to Count Items in a Group Using SQL COUNT function

How to Count Items in a Group Using SQL COUNT function

Summary: in this tutorial, you will learn how to use SQL COUNT aggregate function to get the number of rows in a group.

Introduction to SQL COUNT function

The COUNT function is one of an aggregate function that returns the number of  rows in a group. The following illustrates syntax of using the COUNT function:

COUNT(*)
COUNT(ALL expression)
COUNT(DISTINCT expression)

Let’s examine each form of the COUNT function:

  • COUNT(*) – returns the number of rows in a group including NULL and duplicate values.
  • COUNT(ALL expression) – evaluates the expression for every row in a group and returns the number of rows except NULL values.
  • COUNT(DISTINCT expression) – evaluates the expression for every row in a group and returns the number of rows except NULL and duplicate values.

Let’s practice with a couple of examples to see how the COUNT function works.

The SQL COUNT function examples

Simple SQL COUNT example

We can count the number of orders that has not shipped to the customers by using COUNT function to count all orders that have shipped date column with NULL values.

SELECT COUNT(*) as "pendding order"
FROM orders
WHERE shippeddate IS NULL

SQL Count Example - Pending Orders

SQL COUNT with a GROUP BY clause example

We can count the number of orders that have already shipped by customer name using the COUNT function with GROUP BY clause:

SELECT companyname, COUNT(*) AS "Order Shipped"
FROM orders
INNER JOIN customers on customers.customerid = orders.customerid
WHERE shippeddate IS NOT NULL
GROUP BY companyname

SQL COUNT with a GROUP BY clause

SQL COUNT with a HAVING clause

We can select customer that has number of shipped orders greater than 10 using the SQL COUNT function with HAVING clause as follows:

SELECT companyname, COUNT(*) AS "Order Shipped"
FROM orders
INNER JOIN customers on customers.customerid = orders.customerid
WHERE shippeddate IS NOT NULL
GROUP BY companyname
HAVING COUNT(*) > 10

SQL COUNT with a HAVING clause

SQL COUNT with DISTINCT example

We can also count the number of countries where the customers locates using COUNT DISTINCT as the following query:

SELECT COUNT( DISTINCT country) AS country
FROM customers

SQL Count Example - Customer's Country

In this tutorial, you have learned how to use the SQL COUNT aggregate function to get the number of rows in a group.