🔢 Oracle COUNT() Function Guide
In this tutorial, you'll learn how to use the Oracle COUNT() function to count rows, non-null values, and distinct values in SQL queries.
📌 What is Oracle COUNT()?
The COUNT() function returns the number of rows or non-null values in a column. It’s commonly used for reporting, validation, and analysis in SQL queries.
🧮 Syntax
COUNT({ * | [DISTINCT] expression })
COUNT(*): Counts all rows, including those with NULL values.COUNT(column): Counts only non-null values in the specified column.COUNT(DISTINCT column): Counts only unique, non-null values.
📋 Example: COUNT(*)
This counts all rows in the employees table:
SELECT COUNT(*) AS total_employees FROM employees;
📋 Example: COUNT(column)
This counts only the non-null values in the commission_pct column:
SELECT COUNT(commission_pct) AS non_null_commission FROM employees;
📋 Example: COUNT(DISTINCT column)
This counts only unique department IDs in the employees table:
SELECT COUNT(DISTINCT department_id) AS unique_departments FROM employees;
📊 COUNT() with GROUP BY
Use COUNT() with GROUP BY to count rows per group:
SELECT department_id, COUNT(*) AS employees_per_dept FROM employees GROUP BY department_id;
🔎 COUNT() with HAVING
Filter groups based on row count:
SELECT department_id, COUNT(*) AS total FROM employees GROUP BY department_id HAVING COUNT(*) > 5;
✅ Summary
COUNT(*)counts all rows including NULLs.COUNT(column)ignores NULLs.COUNT(DISTINCT column)returns unique non-null values.- Use with
GROUP BYandHAVINGfor grouped analysis.
With Oracle COUNT(), you can easily analyze the number of records or values in your datasets.