Modules

In SQL, COUNT is a super handy function when you want to know how many rows you've got in a column. It's like asking, "Hey, how many entries are here?".

Suppose we have a table named orders with the following columns and sample data:

• order_id: Unique identifier for each order.
• product_id: Identifier for the product ordered.
• quantity: Number of units of the product ordered.
• product_name: Name of the product.

Using COUNT in SQL Queries:

Count Total Orders: This query counts the total number of orders in the orders table.

SELECT COUNT(*)
FROM orders;

Count Specific Product Orders: This query counts how many orders were placed for 'Espresso'.

SELECT COUNT(*)
FROM orders
WHERE product_name = 'Espresso';

Count with Column Alias: This query counts the total orders and labels the result as total_orders for clarity. We will that alias trick a lot!

SELECT COUNT(*) AS total_orders
FROM orders;

Using COUNT DISTINCT in SQL Queries:

Let's look at a sample of our table again. As you can see below, you can have multiple orders from the same product_id.

Here you can see that order_id numbers 1 and 5 bought an Espresso and you have been asked to count the number of unique products. This is when DISTINCT comes in, and you need to specify which column you want to get the unique value from (in this case product_id).

This is how it looks in SQL:

SELECT COUNT(DISTINCT product_id) AS unique_products
FROM orders;

See I used the alias again and called it unique_products. This will help when you handle long queries to give a name to your columns.

Great job in finishing this section!

Time to practice!