1. COUNT(*) - Counting All Rows

This is the most common form. COUNT(*) counts all rows returned by the query, including rows with NULL values in some or all columns and duplicate rows. It simply counts the number of rows in the result set defined by the FROM and WHERE clauses.

-- Count the total number of interaction records
SELECT COUNT(*) AS total_interactions
FROM Interactions;

Result: A single value representing the total number of rows in the Interactions table.

2. COUNT(column_name) - Counting Non-NULL Values

When you specify a column name inside COUNT(), it counts the number of rows where the specified column_name is not NULL. It ignores rows where that particular column has a NULL value.

-- Count how many interactions include actual comment text
-- (Assuming comment_text is NULL for likes/shares)
SELECT COUNT(comment_text) AS number_of_comments
FROM Interactions;

Result: The count of rows where comment_text is not NULL. This effectively counts only the comment interactions.

3. COUNT(DISTINCT column_name) - Counting Unique Non-NULL Values

To count the number of unique, non-NULL values within a specific column, use the DISTINCT keyword before the column name.

-- Count how many unique users have uploaded videos
SELECT COUNT(DISTINCT user_id) AS distinct_uploaders
FROM Videos;

Result: The count of unique user_id values present in the Videos table. If a user uploaded 10 videos, they are counted only once. NULLs in user_id (if allowed) would be ignored.

4. COUNT(1) or COUNT(constant) - Counting All Rows (Alternative)

COUNT(1) (or COUNT(0), COUNT('any_constant')) behaves identically to COUNT(*). It counts every row returned by the query because the constant expression 1 is never NULL.

-- Count all videos in the platform using COUNT(1)
SELECT COUNT(1) AS total_videos
FROM Videos;

Historically, some believed COUNT(1) was faster than COUNT(*), but most modern database optimizers treat them the same. COUNT(*) is generally preferred for clarity as it explicitly means "count rows".

Basic Platform Overview

Get a quick sense of the scale of the TokTuk platform:

-- Get basic counts for our main entities
SELECT
  (SELECT COUNT(*) FROM Users) AS total_users,
  (SELECT COUNT(*) FROM Videos) AS total_videos,
  (SELECT COUNT(DISTINCT user_id) FROM Videos) AS unique_creators,
  (SELECT COUNT(*) FROM Interactions) AS total_interactions,
  (SELECT COUNT(comment_text) FROM Interactions) AS total_comments;

This uses scalar subqueries to fetch multiple independent counts in a single result row.

Counting with Conditions (WHERE Clause)

Use the WHERE clause to apply conditions before counting:

-- Count videos uploaded in 2023 with more than 1000 views
SELECT COUNT(*) AS popular_2023_videos
FROM Videos
WHERE views > 1000
  AND SUBSTR(upload_date, 1, 4) = '2023'; -- Assuming YYYY-MM-DD format

Only rows meeting the WHERE criteria are considered by COUNT(*).

Conditional Counting (CASE within COUNT)

Use CASE expressions inside COUNT to count based on conditions within the same aggregation scope. This is powerful for pivoting or creating summary statistics.

-- Count different interaction types in a single pass
SELECT
  COUNT(*) AS total_interactions,
  COUNT(CASE WHEN interaction_type = 'like'    THEN 1 ELSE NULL END) AS like_count,
  COUNT(CASE WHEN interaction_type = 'comment' THEN 1 ELSE NULL END) AS comment_count,
  COUNT(CASE WHEN interaction_type = 'share'   THEN 1 ELSE NULL END) AS share_count
FROM Interactions;

The CASE statement returns 1 (a non-NULL value) when the condition is met, and NULL otherwise. COUNT(expression) only counts the non-NULL results, giving us separate counts for each type.

Counting Items Per Category

Find out how many items belong to each category.

-- Count videos uploaded by each user
SELECT
  user_id,
  COUNT(*) AS number_of_videos_uploaded -- Count all rows within each user_id group
FROM Videos
GROUP BY user_id
ORDER BY number_of_videos_uploaded DESC;

This query groups rows by user_id and then COUNT(*) counts the rows within each group, revealing the most active creators.

Counting Unique Items Per Category

Find the number of distinct items within each group.

-- Count how many unique videos each user has interacted with
SELECT
  user_id,
  COUNT(DISTINCT video_id) AS unique_videos_interacted_with
FROM Interactions
GROUP BY user_id
ORDER BY unique_videos_interacted_with DESC;

Here, COUNT(DISTINCT video_id) counts the unique videos each user engaged with, even if they liked the same video multiple times.

Combining with Joins for Richer Context

Join tables before grouping to include descriptive information.

-- Count videos per user, showing username
SELECT
  u.username,
  COUNT(v.video_id) AS video_count -- COUNT(v.video_id) avoids counting NULLs from LEFT JOIN
FROM Users u
LEFT JOIN Videos v ON u.user_id = v.user_id
GROUP BY u.user_id, u.username -- Group by PK and any other non-aggregated columns selected
ORDER BY video_count DESC;

Using LEFT JOIN ensures all users are listed, even those with 0 videos. Using COUNT(v.video_id) correctly counts 0 for users with no matching videos (as v.video_id would be NULL). COUNT(*) would incorrectly count 1 for users with no videos due to the row generated by the LEFT JOIN.

Filtering Grouped Counts with HAVING

Use the HAVING clause to filter groups based on the result of the COUNT function.

-- Find users who have uploaded more than 5 videos
SELECT
  u.username,
  COUNT(v.video_id) AS video_count
FROM Users u
JOIN Videos v ON u.user_id = v.user_id -- Use JOIN if we only care about users WITH videos
GROUP BY u.user_id, u.username
HAVING COUNT(v.video_id) > 5 -- Filter groups AFTER aggregation
ORDER BY video_count DESC;