PostgreSQL COUNT

Key Concepts

SELECT COUNT(*) FROM customers;

SELECT COUNT(email) FROM customers;

SELECT COUNT(DISTINCT city) FROM customers;

SELECT country, COUNT(*) FROM customers GROUP BY country;

🔹 Basic COUNT(*)

COUNT(*) returns the total number of rows in a table or result set, including rows with NULL values. It's the most common way to count records and is useful for getting table sizes, result set sizes, or verifying data imports and deletions.

-- Count all customers
SELECT COUNT(*) AS total_customers 
FROM customers;

-- Count all orders
SELECT COUNT(*) AS total_orders 
FROM orders;

-- Count products in stock
SELECT COUNT(*) AS products_in_stock 
FROM products 
WHERE stock > 0;

🔹 COUNT(column_name)

COUNT(column_name) counts only non-NULL values in the specified column. This is useful for finding how many rows have actual data in a particular field, helping identify missing data or measure data completeness. NULL values are automatically excluded from the count.

-- Count customers with phone numbers
SELECT COUNT(phone) AS customers_with_phone 
FROM customers;

-- Count orders with tracking numbers
SELECT COUNT(tracking_number) AS orders_with_tracking 
FROM orders;

-- Compare total rows vs non-NULL values
SELECT 
    COUNT(*) AS total_customers,
    COUNT(phone) AS with_phone,
    COUNT(*) - COUNT(phone) AS without_phone 
FROM customers;

🔹 COUNT(DISTINCT column)

COUNT(DISTINCT) counts only unique values in a column, automatically removing duplicates before counting. This is perfect for finding how many different cities, categories, or unique values exist in your data. It combines the power of DISTINCT with counting functionality.

-- Count unique countries
SELECT COUNT(DISTINCT country) AS unique_countries 
FROM customers;

-- Count unique product categories
SELECT COUNT(DISTINCT category) AS unique_categories 
FROM products;

-- Count unique customers who placed orders
SELECT COUNT(DISTINCT customer_id) AS unique_customers 
FROM orders;

-- Compare total vs unique values
SELECT 
    COUNT(city) AS total_entries,
    COUNT(DISTINCT city) AS unique_cities 
FROM customers;

🔹 COUNT with WHERE

Combining COUNT with WHERE lets you count rows that meet specific conditions. The WHERE clause filters data first, then COUNT operates on the filtered results. This is essential for conditional counting, like counting active users, pending orders, or products in specific price ranges.

-- Count customers from USA
SELECT COUNT(*) AS usa_customers 
FROM customers 
WHERE country = 'USA';

-- Count expensive products
SELECT COUNT(*) AS expensive_products 
FROM products 
WHERE price > 500;

-- Count pending orders
SELECT COUNT(*) AS pending_orders 
FROM orders 
WHERE status = 'Pending';

-- Count active users registered this year
SELECT COUNT(*) AS new_active_users 
FROM users 
WHERE status = 'active' 
AND EXTRACT(YEAR FROM created_at) = 2024;

🔹 COUNT with GROUP BY

Using COUNT with GROUP BY counts rows for each distinct group separately. This powerful combination creates frequency distributions, showing how many items exist in each category, how many orders per customer, or any grouped counting. Each group gets its own independent count.

-- Count customers per country
SELECT 
    country,
    COUNT(*) AS customer_count 
FROM customers 
GROUP BY country 
ORDER BY customer_count DESC;

-- Count products per category
SELECT 
    category,
    COUNT(*) AS product_count 
FROM products 
GROUP BY category;

-- Count orders per status
SELECT 
    status,
    COUNT(*) AS order_count 
FROM orders 
GROUP BY status;

-- Count employees per department
SELECT 
    department,
    COUNT(*) AS employee_count 
FROM employees 
GROUP BY department 
ORDER BY employee_count DESC;

🔹 COUNT with HAVING

HAVING filters groups after GROUP BY and COUNT operations, unlike WHERE which filters before grouping. Use HAVING to find groups that meet count criteria, like categories with more than 10 products or customers with multiple orders. It's essential for aggregate-based filtering.

-- Find countries with more than 100 customers
SELECT 
    country,
    COUNT(*) AS customer_count 
FROM customers 
GROUP BY country 
HAVING COUNT(*) > 100;

-- Find categories with at least 20 products
SELECT 
    category,
    COUNT(*) AS product_count 
FROM products 
GROUP BY category 
HAVING COUNT(*) >= 20;

-- Find customers with more than 5 orders
SELECT 
    customer_id,
    COUNT(*) AS order_count 
FROM orders 
GROUP BY customer_id 
HAVING COUNT(*) > 5 
ORDER BY order_count DESC;

🔹 Multiple COUNT Functions

You can use multiple COUNT functions in a single query to count different things simultaneously. This is efficient for creating comprehensive reports showing various metrics at once. Each COUNT operates independently, allowing complex analytical queries with multiple counting criteria in one statement.

-- Count different order statuses
SELECT 
    COUNT(*) AS total_orders,
    COUNT(CASE WHEN status = 'Pending' THEN 1 END) AS pending_orders,
    COUNT(CASE WHEN status = 'Shipped' THEN 1 END) AS shipped_orders,
    COUNT(CASE WHEN status = 'Delivered' THEN 1 END) AS delivered_orders 
FROM orders;

-- Count products by price range
SELECT 
    COUNT(*) AS total_products,
    COUNT(CASE WHEN price < 50 THEN 1 END) AS budget_products,
    COUNT(CASE WHEN price BETWEEN 50 AND 200 THEN 1 END) AS mid_range,
    COUNT(CASE WHEN price > 200 THEN 1 END) AS premium_products 
FROM products;

-- Customer data completeness
SELECT 
    COUNT(*) AS total_customers,
    COUNT(email) AS with_email,
    COUNT(phone) AS with_phone,
    COUNT(address) AS with_address 
FROM customers;

🔹 Combining COUNT with Other Aggregates

COUNT works seamlessly with other aggregate functions like SUM, AVG, MIN, and MAX to provide comprehensive statistical analysis. This combination gives you complete insights into your data, showing not just quantities but also totals, averages, and ranges all in one efficient query.

-- Complete product statistics
SELECT 
    COUNT(*) AS total_products,
    COUNT(DISTINCT category) AS unique_categories,
    MIN(price) AS cheapest,
    MAX(price) AS most_expensive,
    AVG(price) AS average_price,
    SUM(stock) AS total_stock 
FROM products;

-- Order statistics by customer
SELECT 
    customer_id,
    COUNT(*) AS order_count,
    SUM(total) AS total_spent,
    AVG(total) AS avg_order_value,
    MIN(order_date) AS first_order,
    MAX(order_date) AS last_order 
FROM orders 
GROUP BY customer_id 
HAVING COUNT(*) > 3;