What is the difference between COUNT and SUM functions?

he COUNT and SUM functions in SQL are both aggregate functions, but they serve different purposes:

1. COUNT

• Purpose: Counts the number of rows that meet a certain condition.
• Usage: Useful for finding the number of records, entries, or occurrences.
• Example:

SELECT COUNT(*) AS total_employees
FROM employees
WHERE department = 'IT';

This returns the total number of employees in the IT department.

You can also count distinct values:

SELECT COUNT(DISTINCT department) AS unique_departments
FROM employees;

This counts how many unique departments exist.

2. SUM

• Purpose: Adds up the numeric values of a column.
• Usage: Useful for calculating totals, like sales, salaries, or quantities.
• Example:

SELECT SUM(salary) AS total_salary
FROM employees
WHERE department = 'IT';

This returns the total salary of all employees in the IT department.

Real-world scenario:
I used COUNT to determine the number of customers who made purchases in a given month, and SUM to calculate the total revenue generated in that same period. Both were essential for monthly sales reporting dashboards.

Challenges:

• COUNT includes NULL handling nuances: COUNT(column_name) ignores NULLs, while COUNT(*) counts all rows. Forgetting this can lead to inaccurate results.
• SUM requires numeric columns — attempting to sum non-numeric data will result in errors.

Limitations:

• Both aggregate functions return a single value per group, so if you need row-level details alongside aggregates, you may need GROUP BY or window functions.

Alternative/Enhancement:

• For combined statistics, I sometimes use COUNT and SUM together with GROUP BY:

SELECT department, COUNT(*) AS num_employees, SUM(salary) AS total_salary
FROM employees
GROUP BY department;

This provides both the number of employees and total salary per department in one query.

In summary, COUNT is for counting rows, while SUM is for adding numeric values. They are often used together in reporting and analytics scenarios.