COUNT(*) vs COUNT(column) difference in SQL - Hands-On Comparison

Understanding COUNT(*) vs COUNT(column) in SQL

📖 Scenario: You work at a small bookstore. You have a table called sales that records each book sale. Some sales have a recorded discount_code, but some do not.You want to learn how to count total sales and how to count only sales where a discount code was used.

🎯 Goal: Build SQL queries to count total sales and count sales with discount codes, so you understand the difference between COUNT(*) and COUNT(discount_code).

📋 What You'll Learn

Create a table called sales with columns sale_id (integer) and discount_code (text, nullable).

Insert 5 rows into sales with some discount_code values NULL and some with text.

Write a query using COUNT(*) to count all sales.

Write a query using COUNT(discount_code) to count only sales with a discount code.

💡 Why This Matters

🌍 Real World

Counting total records and filtering counts based on column values is common in sales, inventory, and user data analysis.

💼 Career

Understanding COUNT(*) vs COUNT(column) helps in writing accurate SQL queries for reports and data insights in many data-related jobs.

1

Create the sales table and insert data

Create a table called sales with columns sale_id as integer and discount_code as text. Then insert these exact rows: (1, 'DISC10'), (2, NULL), (3, 'DISC20'), (4, NULL), (5, 'DISC30').

SQL

-- Create the sales table and insert data
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Write a query to count all sales using COUNT(*)

Write a SQL query that selects COUNT(*) from the sales table to count all rows, including those with NULL discount_code.

SQL

CREATE TABLE sales (
  sale_id INTEGER,
  discount_code TEXT
);

INSERT INTO sales (sale_id, discount_code) VALUES
  (1, 'DISC10'),
  (2, NULL),
  (3, 'DISC20'),
  (4, NULL),
  (5, 'DISC30');

-- Write query to count all sales using COUNT(*)
-- Your code here

Need a hint?

COUNT(*) counts all rows regardless of NULLs.

3

Write a query to count only sales with a discount code using COUNT(discount_code)

Write a SQL query that selects COUNT(discount_code) from the sales table to count only rows where discount_code is not NULL.

SQL

CREATE TABLE sales (
  sale_id INTEGER,
  discount_code TEXT
);

INSERT INTO sales (sale_id, discount_code) VALUES
  (1, 'DISC10'),
  (2, NULL),
  (3, 'DISC20'),
  (4, NULL),
  (5, 'DISC30');

SELECT COUNT(*) FROM sales;

-- Write query to count only sales with discount_code using COUNT(discount_code)
-- Your code here

Need a hint?

COUNT(column) counts only rows where the column is not NULL.

4

Add a query to show both counts side by side

Write a SQL query that selects both COUNT(*) and COUNT(discount_code) from the sales table in the same result, labeling them as total_sales and sales_with_discount.

SQL

CREATE TABLE sales (
  sale_id INTEGER,
  discount_code TEXT
);

INSERT INTO sales (sale_id, discount_code) VALUES
  (1, 'DISC10'),
  (2, NULL),
  (3, 'DISC20'),
  (4, NULL),
  (5, 'DISC30');

SELECT COUNT(*) FROM sales;

SELECT COUNT(discount_code) FROM sales;

-- Write query to select COUNT(*) as total_sales and COUNT(discount_code) as sales_with_discount
-- Your code here

Need a hint?

Use AS to label columns in the result.