Lesson 4.1

Counting Rows with SQL COUNT

COUNT is the first aggregate most SQL learners need: it answers how many.

Aggregate functions summarize many rows into fewer rows. COUNT answers a simple but common question: how many rows are there?

count(*) counts every row. count(column_name) only counts rows where that column has a value.

Pick the count that matches the question

Use count(*) when the question is about rows. Use count(column) when the question is about known values in one column. That difference matters whenever a column can be null.

Pattern

select count(*) as total_rows,
       count(nullable_column) as rows_with_value
from table_name

Ready to run it?

Open the DuckDB playground with the matching dataset and query already filled in.

Try COUNT rows in the playground

Schema · Garden ShopTable · orders6 columns · 24 rows

Table · orders

One row per order. Unshipped orders have a null shipped_date.

6 columns · 24 rows

order_id intcustomer_id intorder_date dateshipped_date datestatus textcoupon_code text

Your task

Return one summary row showing the total number of ordersand the number of orders where shipped date is present.

SQL Workbench

query.sqlgarden_shop · SQL engine loading

⌘↵ to run

Expected answer

Columns: total_orders, orders_with_ship_date.
Rows: 1 summary row.
Expected values: 24 total orders and 17 orders with a ship date.

← Back to all lessons

Next · 4.2 SUM and AVG →

Lesson

SQL SUM and AVG Aggregates

Calculate totals and averages.

Lesson

SQL GROUP BY: Grouping Rows

Summarize rows by category.

Lesson

SQL HAVING: Filtering Groups

Filter after GROUP BY.

Reference

SQL Aggregate Functions

COUNT, SUM, AVG, MIN, MAX, GROUP BY, and HAVING.

Pick the count that matches the question

Related