SQL Avg

Last modified on: December 3, 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="2bf20a8c-2ce1-4a71-a2a9-4aaa343dc40f" data-message-model-slug="gpt-4o-mini">
<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 AVG Function: A Complete Guide</strong></h3>
<h4><strong>What is the SQL AVG Function?</strong></h4>
<p>The <strong><code>AVG()</code></strong> function in SQL is used to calculate the average value of a numeric column. It is commonly used to find the mean of data, such as the average salary, average sales, or average quantity. Like other aggregate functions, it can be used with <strong><code>GROUP BY</code></strong> to calculate averages for different categories.</p>
<hr />
<h3><strong>Syntax</strong></h3>
<h4><strong>Basic Syntax</strong></h4>
<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(column_name)
FROM table_name
WHERE condition;
</code></pre>
</div>
</div>
</div>
</div>
<h4><strong>AVG with GROUP BY</strong></h4>
<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 column_name, AVG(column_name)
FROM table_name
GROUP BY column_name;
</code></pre>
</div>
</div>
</div>
</div>
<h4><strong>AVG with HAVING</strong></h4>
<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 column_name, AVG(column_name)
FROM table_name
GROUP BY column_name
HAVING AVG(column_name) &gt; some_value;
</code></pre>
</div>
</div>
</div>
</div>
<hr />
<h3><strong>Examples of SQL AVG</strong></h3>
<h4><strong>1. Basic AVG Example</strong></h4>
<p><strong>Example: Calculate the average salary of all employees</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>Output Example:</strong></p>
<table>
<thead>
<tr>
<th>average_salary</th>
</tr>
</thead>
<tbody>
<tr>
<td>50,000</td>
</tr>
</tbody>
</table>
<hr />
<h4><strong>2. AVG with a Condition</strong></h4>
<p><strong>Example: Calculate the average salary of employees in the IT 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 AVG(salary) AS average_it_salary
FROM employees
WHERE department = 'IT';
</code></pre>
</div>
</div>
</div>
</div>
<hr />
<h4><strong>3. AVG with GROUP BY</strong></h4>
<p>You can use <strong><code>GROUP BY</code></strong> to calculate the average for different groups, such as departments or positions.</p>
<p><strong>Example: Average 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, AVG(salary) AS average_salary
FROM employees
GROUP BY department;
</code></pre>
</div>
</div>
</div>
</div>
<p><strong>Output Example:</strong></p>
<table>
<thead>
<tr>
<th>department</th>
<th>average_salary</th>
</tr>
</thead>
<tbody>
<tr>
<td>IT</td>
<td>60,000</td>
</tr>
<tr>
<td>HR</td>
<td>55,000</td>
</tr>
<tr>
<td>Sales</td>
<td>50,000</td>
</tr>
</tbody>
</table>
<hr />
<h4><strong>4. AVG with HAVING</strong></h4>
<p>The <strong><code>HAVING</code></strong> clause allows you to filter groups based on the average value.</p>
<p><strong>Example: Departments with an average salary greater than 50,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, AVG(salary) AS average_salary
FROM employees
GROUP BY department
HAVING AVG(salary) &gt; 50000;
</code></pre>
</div>
</div>
</div>
</div>
<p><strong>Output Example:</strong></p>
<table>
<thead>
<tr>
<th>department</th>
<th>average_salary</th>
</tr>
</thead>
<tbody>
<tr>
<td>IT</td>
<td>60,000</td>
</tr>
<tr>
<td>HR</td>
<td>55,000</td>
</tr>
</tbody>
</table>
<hr />
<h4><strong>5. AVG with DISTINCT</strong></h4>
<p>The <strong><code>DISTINCT</code></strong> keyword can be used with <strong><code>AVG()</code></strong> to calculate the average of unique, non-NULL values.</p>
<p><strong>Example: Calculate the average 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 AVG(DISTINCT salary) AS average_unique_salary
FROM employees;
</code></pre>
</div>
</div>
</div>
</div>
<hr />
<h3><strong>AVG with Joins</strong></h3>
<p>You can also use <strong><code>AVG()</code></strong> in queries that involve joins to calculate averages based on related data.</p>
<p><strong>Example: Calculate the average order amount by customer</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 customers.name, AVG(orders.amount) AS average_order_amount
FROM customers
JOIN orders ON customers.id = orders.customer_id
GROUP BY customers.name;
</code></pre>
</div>
</div>
</div>
</div>
<hr />
<h3><strong>Performance Tips for AVG</strong></h3>
<ol>
<li><strong>Indexing</strong>: Ensure the column you're calculating the average for is indexed, especially in large datasets.</li>
<li><strong>Filter Data Early</strong>: Use <strong><code>WHERE</code></strong> to limit the dataset before applying the <strong><code>AVG()</code></strong> function.</li>
<li><strong>GROUP BY Optimization</strong>: Ensure the columns you are grouping by are indexed for faster performance.</li>
</ol>
<hr />
<h3><strong>Key Points to Remember</strong></h3>
<ul>
<li><strong><code>AVG()</code></strong> works only with numeric columns and ignores <strong>NULL</strong> values.</li>
<li><strong><code>AVG(DISTINCT column_name)</code></strong> calculates the average of unique, non-NULL values in a column.</li>
<li>Use <strong><code>GROUP BY</code></strong> to calculate averages for different groups or categories within your data.</li>
</ul>
<hr />
<h3>&nbsp;</h3>
</div>
</div>
</div>
</div>
</div>
</div>

Web Technology View More

SQL Avg

Learn Ansible

Learn HTML

Learn Angular 3

Learn PHP

Learn Angular 2.0

Learn Python

Learn C# Sharp

Learn CSS

Learn Node Js

Learn JQuery Framework

Learn SQL Tutorial

Learn MySql

Learn PHP 7.1

Learn Angular 3

Learn Angular 2

Learn HTML5

Learn Angular 2

Learn Angular 3.0

Learn Bootstrap 3

Learn Bootstrap 4

Learn Bootstrap 5

Learn JQuery Tutorials