SQL Aggregate Functions

Last modified on: December 2, 2024

<div class="flex-shrink-0 flex flex-col relative items-end">
<div>
<div class="pt-0">
<div class="gizmo-bot-avatar flex h-8 w-8 items-center justify-center overflow-hidden rounded-full">&nbsp;</div>
</div>
</div>
</div>
<div class="group/conversation-turn relative flex w-full min-w-0 flex-col agent-turn">
<div class="flex-col gap-1 md:gap-3">
<div class="flex max-w-full flex-col flex-grow">
<div class="min-h-8 text-message flex w-full flex-col items-end gap-2 whitespace-normal break-words [.text-message+&amp;]:mt-5" dir="auto" data-message-author-role="assistant" data-message-id="e39ef072-99ef-4505-98fb-cf860b539c30" data-message-model-slug="gpt-4o">
<div class="flex w-full flex-col gap-1 empty:hidden first:pt-[3px]">
<div class="markdown prose w-full break-words dark:prose-invert light">
<h3><strong>SQL Aggregate Functions: A Complete Guide</strong></h3>
<h4><strong>What are SQL Aggregate Functions?</strong></h4>
<p>SQL <strong>aggregate functions</strong> perform calculations on a set of values and return a single result. These functions are often used in conjunction with the <strong><code>GROUP BY</code></strong> clause to summarize data in a table.</p>
<hr />
<h3><strong>List of Aggregate Functions</strong></h3>
<ol>
<li><strong><code>COUNT()</code></strong>: Counts the number of rows.</li>
<li><strong><code>SUM()</code></strong>: Calculates the total sum of a numeric column.</li>
<li><strong><code>AVG()</code></strong>: Calculates the average value of a numeric column.</li>
<li><strong><code>MIN()</code></strong>: Returns the smallest value in a column.</li>
<li><strong><code>MAX()</code></strong>: Returns the largest value in a column.</li>
</ol>
<hr />
<h3><strong>Examples of SQL Aggregate Functions</strong></h3>
<h4><strong>1. COUNT()</strong></h4>
<p>Counts the number of rows or non-NULL values in a column.</p>
<p><strong>Example: Count all employees in a table</strong></p>
<div class="contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative bg-token-sidebar-surface-primary dark:bg-gray-950">
<div class="flex items-center text-token-text-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9 bg-token-sidebar-surface-primary dark:bg-token-main-surface-secondary select-none">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-sidebar-surface-primary px-2 font-sans text-xs text-token-text-secondary dark:bg-token-main-surface-secondary">
<pre class="language-php"><code>SELECT COUNT(*) AS total_employees
FROM employees;
</code></pre>
</div>
</div>
</div>
</div>
<p><strong>Example: Count employees with a specific position</strong></p>
<div class="contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative bg-token-sidebar-surface-primary dark:bg-gray-950">
<div class="flex items-center text-token-text-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9 bg-token-sidebar-surface-primary dark:bg-token-main-surface-secondary select-none">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-sidebar-surface-primary px-2 font-sans text-xs text-token-text-secondary dark:bg-token-main-surface-secondary">
<pre class="language-php"><code>SELECT COUNT(*) AS manager_count
FROM employees
WHERE position = 'Manager';
</code></pre>
</div>
</div>
</div>
</div>
<hr />
<h4><strong>2. SUM()</strong></h4>
<p>Calculates the sum of a numeric column.</p>
<p><strong>Example: Calculate the total salary</strong></p>
<div class="contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative bg-token-sidebar-surface-primary dark:bg-gray-950">
<div class="flex items-center text-token-text-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9 bg-token-sidebar-surface-primary dark:bg-token-main-surface-secondary select-none">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-sidebar-surface-primary px-2 font-sans text-xs text-token-text-secondary dark:bg-token-main-surface-secondary">
<pre class="language-php"><code>SELECT SUM(salary) AS total_salary
FROM employees;
</code></pre>
</div>
</div>
</div>
</div>
<p><strong>Example: Calculate total salary by department</strong></p>
<div class="contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative bg-token-sidebar-surface-primary dark:bg-gray-950">
<div class="flex items-center text-token-text-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9 bg-token-sidebar-surface-primary dark:bg-token-main-surface-secondary select-none">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-sidebar-surface-primary px-2 font-sans text-xs text-token-text-secondary dark:bg-token-main-surface-secondary">
<pre class="language-php"><code>SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department;
</code></pre>
</div>
</div>
</div>
</div>
<hr />
<h4><strong>3. AVG()</strong></h4>
<p>Calculates the average value of a numeric column.</p>
<p><strong>Example: Calculate the average salary</strong></p>
<div class="contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative bg-token-sidebar-surface-primary dark:bg-gray-950">
<div class="flex items-center text-token-text-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9 bg-token-sidebar-surface-primary dark:bg-token-main-surface-secondary select-none">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-sidebar-surface-primary px-2 font-sans text-xs text-token-text-secondary dark:bg-token-main-surface-secondary">
<pre class="language-php"><code>SELECT AVG(salary) AS average_salary
FROM employees;
</code></pre>
</div>
</div>
</div>
</div>
<p><strong>Example: Calculate the average salary by position</strong></p>
<div class="contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative bg-token-sidebar-surface-primary dark:bg-gray-950">
<div class="flex items-center text-token-text-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9 bg-token-sidebar-surface-primary dark:bg-token-main-surface-secondary select-none">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-sidebar-surface-primary px-2 font-sans text-xs text-token-text-secondary dark:bg-token-main-surface-secondary">
<pre class="language-php"><code>SELECT position, AVG(salary) AS average_salary
FROM employees
GROUP BY position;
</code></pre>
</div>
</div>
</div>
</div>
<hr />
<h4><strong>4. MIN()</strong></h4>
<p>Finds the smallest value in a column.</p>
<p><strong>Example: Find the minimum salary</strong></p>
<div class="contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative bg-token-sidebar-surface-primary dark:bg-gray-950">
<div class="flex items-center text-token-text-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9 bg-token-sidebar-surface-primary dark:bg-token-main-surface-secondary select-none">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-sidebar-surface-primary px-2 font-sans text-xs text-token-text-secondary dark:bg-token-main-surface-secondary">
<pre class="language-php"><code>SELECT MIN(salary) AS minimum_salary
FROM employees;
</code></pre>
</div>
</div>
</div>
</div>
<p><strong>Example: Find the minimum salary by department</strong></p>
<div class="contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative bg-token-sidebar-surface-primary dark:bg-gray-950">
<div class="flex items-center text-token-text-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9 bg-token-sidebar-surface-primary dark:bg-token-main-surface-secondary select-none">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-sidebar-surface-primary px-2 font-sans text-xs text-token-text-secondary dark:bg-token-main-surface-secondary">
<pre class="language-php"><code>SELECT department, MIN(salary) AS minimum_salary
FROM employees
GROUP BY department;
</code></pre>
</div>
</div>
</div>
</div>
<hr />
<h4><strong>5. MAX()</strong></h4>
<p>Finds the largest value in a column.</p>
<p><strong>Example: Find the maximum salary</strong></p>
<div class="contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative bg-token-sidebar-surface-primary dark:bg-gray-950">
<div class="flex items-center text-token-text-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9 bg-token-sidebar-surface-primary dark:bg-token-main-surface-secondary select-none">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-sidebar-surface-primary px-2 font-sans text-xs text-token-text-secondary dark:bg-token-main-surface-secondary">
<pre class="language-php"><code>SELECT MAX(salary) AS maximum_salary
FROM employees;
</code></pre>
</div>
</div>
</div>
</div>
<p><strong>Example: Find the maximum salary by position</strong></p>
<div class="contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative bg-token-sidebar-surface-primary dark:bg-gray-950">
<div class="flex items-center text-token-text-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9 bg-token-sidebar-surface-primary dark:bg-token-main-surface-secondary select-none">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-sidebar-surface-primary px-2 font-sans text-xs text-token-text-secondary dark:bg-token-main-surface-secondary">
<pre class="language-php"><code>SELECT position, MAX(salary) AS maximum_salary
FROM employees
GROUP BY position;
</code></pre>
</div>
</div>
</div>
</div>
<hr />
<h3><strong>Using Aggregate Functions with GROUP BY</strong></h3>
<p>The <strong><code>GROUP BY</code></strong> clause groups rows that have the same values in specified columns, allowing aggregate functions to summarize data for each group.</p>
<p><strong>Example: Total salary by department</strong></p>
<div class="contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative bg-token-sidebar-surface-primary dark:bg-gray-950">
<div class="flex items-center text-token-text-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9 bg-token-sidebar-surface-primary dark:bg-token-main-surface-secondary select-none">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-sidebar-surface-primary px-2 font-sans text-xs text-token-text-secondary dark:bg-token-main-surface-secondary">
<pre class="language-php"><code>SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department;
</code></pre>
</div>
</div>
</div>
</div>
<p><strong>Example: Count employees by position</strong></p>
<div class="contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative bg-token-sidebar-surface-primary dark:bg-gray-950">
<div class="flex items-center text-token-text-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9 bg-token-sidebar-surface-primary dark:bg-token-main-surface-secondary select-none">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-sidebar-surface-primary px-2 font-sans text-xs text-token-text-secondary dark:bg-token-main-surface-secondary">
<pre class="language-php"><code>SELECT position, COUNT(*) AS employee_count
FROM employees
GROUP BY position;
</code></pre>
</div>
</div>
</div>
</div>
<hr />
<h3><strong>Using Aggregate Functions with HAVING</strong></h3>
<p>The <strong><code>HAVING</code></strong> clause filters groups created by <code>GROUP BY</code>.</p>
<p><strong>Example: Departments with total salaries above 100,000</strong></p>
<div class="contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative bg-token-sidebar-surface-primary dark:bg-gray-950">
<div class="flex items-center text-token-text-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9 bg-token-sidebar-surface-primary dark:bg-token-main-surface-secondary select-none">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-sidebar-surface-primary px-2 font-sans text-xs text-token-text-secondary dark:bg-token-main-surface-secondary">
<pre class="language-php"><code>SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department
HAVING SUM(salary) &gt; 100000;
</code></pre>
</div>
</div>
</div>
</div>
<hr />
<h3><strong>Aggregate Functions with DISTINCT</strong></h3>
<p>To operate only on unique values, use <strong><code>DISTINCT</code></strong>.</p>
<p><strong>Example: Calculate the sum of unique salaries</strong></p>
<div class="contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative bg-token-sidebar-surface-primary dark:bg-gray-950">
<div class="flex items-center text-token-text-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9 bg-token-sidebar-surface-primary dark:bg-token-main-surface-secondary select-none">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-sidebar-surface-primary px-2 font-sans text-xs text-token-text-secondary dark:bg-token-main-surface-secondary">
<pre class="language-php"><code>SELECT SUM(DISTINCT salary) AS unique_salary_total
FROM employees;
</code></pre>
</div>
</div>
</div>
</div>
<p><strong>Example: Count unique job positions</strong></p>
<div class="contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative bg-token-sidebar-surface-primary dark:bg-gray-950">
<div class="flex items-center text-token-text-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9 bg-token-sidebar-surface-primary dark:bg-token-main-surface-secondary select-none">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-sidebar-surface-primary px-2 font-sans text-xs text-token-text-secondary dark:bg-token-main-surface-secondary">
<pre class="language-php"><code>SELECT COUNT(DISTINCT position) AS unique_positions
FROM employees;
</code></pre>
</div>
</div>
</div>
</div>
<hr />
<h3><strong>Key Points to Remember</strong></h3>
<ol>
<li>Aggregate functions ignore <strong><code>NULL</code></strong> values except for <strong><code>COUNT(*)</code></strong>.</li>
<li>Use <strong><code>GROUP BY</code></strong> to apply aggregate functions to subsets of data.</li>
<li>Combine with <strong><code>HAVING</code></strong> for conditional group filtering.</li>
</ol>
<hr />
<h3>&nbsp;</h3>
</div>
</div>
</div>
</div>
</div>
</div>

Web Technology View More