SQL Group By

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="cf3f2ee1-397f-47fc-a43e-242c94d9e0d4" 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 GROUP BY: A Complete Guide</strong></h3>
<h4><strong>What is SQL GROUP BY?</strong></h4>
<p>The <strong>SQL GROUP BY</strong> clause is used to arrange identical data into groups. It is often used with aggregate functions like <code>COUNT()</code>, <code>SUM()</code>, <code>AVG()</code>, <code>MAX()</code>, and <code>MIN()</code> to perform calculations on each group of data.</p>
<hr />
<h3><strong>Syntax of SQL GROUP BY</strong></h3>
<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, aggregate_function(column_name)
FROM table_name
WHERE condition
GROUP BY column_name;
</code></pre>
</div>
</div>
</div>
</div>
<ul>
<li><strong><code>column_name</code></strong>: The column you want to group by.</li>
<li><strong><code>aggregate_function</code></strong>: A function like <code>SUM()</code>, <code>COUNT()</code>, etc., applied to the grouped data.</li>
<li><strong><code>table_name</code></strong>: The table containing the data.</li>
<li><strong><code>WHERE</code></strong>: Optional clause to filter rows before grouping.</li>
</ul>
<hr />
<h3><strong>How GROUP BY Works</strong></h3>
<ol>
<li><strong>Groups Data:</strong> Rows with the same value in the specified column(s) are grouped together.</li>
<li><strong>Applies Aggregate Functions:</strong> Calculates aggregate values for each group.</li>
</ol>
<hr />
<h3><strong>Example of SQL GROUP BY</strong></h3>
<h4><strong>Scenario: Counting Employees by Department</strong></h4>
<p>You have an <code>employees</code> table and want to count the number of employees in each department.</p>
<h4><strong>Table: employees</strong></h4>
<table>
<thead>
<tr>
<th>employee_id</th>
<th>employee_name</th>
<th>department</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>John Smith</td>
<td>IT</td>
</tr>
<tr>
<td>2</td>
<td>Jane Doe</td>
<td>HR</td>
</tr>
<tr>
<td>3</td>
<td>Mike Johnson</td>
<td>IT</td>
</tr>
<tr>
<td>4</td>
<td>Emily White</td>
<td>HR</td>
</tr>
<tr>
<td>5</td>
<td>David Brown</td>
<td>Sales</td>
</tr>
</tbody>
</table>
<h4><strong>GROUP BY Query Example:</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 department, COUNT(employee_id) AS employee_count
FROM employees
GROUP BY department;
</code></pre>
</div>
</div>
</div>
</div>
<p><strong>Output Example:</strong></p>
<table>
<thead>
<tr>
<th>department</th>
<th>employee_count</th>
</tr>
</thead>
<tbody>
<tr>
<td>IT</td>
<td>2</td>
</tr>
<tr>
<td>HR</td>
<td>2</td>
</tr>
<tr>
<td>Sales</td>
<td>1</td>
</tr>
</tbody>
</table>
<hr />
<h3><strong>Using GROUP BY with Multiple Columns</strong></h3>
<p>You can group by multiple columns to create more granular groupings.</p>
<h4><strong>Example:</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 department, employee_name, COUNT(employee_id) AS employee_count
FROM employees
GROUP BY department, employee_name;
</code></pre>
</div>
</div>
</div>
</div>
<p>This groups by both <code>department</code> and <code>employee_name</code>.</p>
<hr />
<h3><strong>GROUP BY with HAVING Clause</strong></h3>
<p>The <strong>HAVING</strong> clause is used to filter groups after the grouping has been performed. It works like <code>WHERE</code>, but for aggregated data.</p>
<h4><strong>Example: Departments with More than 1 Employee</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 department, COUNT(employee_id) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(employee_id) &gt; 1;
</code></pre>
</div>
</div>
</div>
</div>
<p><strong>Output Example:</strong></p>
<table>
<thead>
<tr>
<th>department</th>
<th>employee_count</th>
</tr>
</thead>
<tbody>
<tr>
<td>IT</td>
<td>2</td>
</tr>
<tr>
<td>HR</td>
<td>2</td>
</tr>
</tbody>
</table>
<hr />
<h3><strong>GROUP BY with Aggregate Functions</strong></h3>
<p>Here are examples of using <code>GROUP BY</code> with common aggregate functions:</p>
<h4><strong>SUM() Example</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 department, SUM(salary) AS total_salary
FROM employees
GROUP BY department;
</code></pre>
</div>
</div>
</div>
</div>
<h4><strong>AVG() Example</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 department, AVG(salary) AS average_salary
FROM employees
GROUP BY department;
</code></pre>
</div>
</div>
</div>
</div>
<h4><strong>MIN() and MAX() Example</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 department, MIN(salary) AS min_salary, MAX(salary) AS max_salary
FROM employees
GROUP BY department;
</code></pre>
</div>
</div>
</div>
</div>
<hr />
<h3><strong>Key Points to Remember</strong></h3>
<ul>
<li>The columns in the <code>SELECT</code> statement must either appear in the <code>GROUP BY</code> clause or be used with an aggregate function.</li>
<li>The <code>HAVING</code> clause filters grouped data, while the <code>WHERE</code> clause filters individual rows before grouping.</li>
</ul>
<hr />
<h3>&nbsp;</h3>
</div>
</div>
</div>
</div>
</div>
</div>

Web Technology View More

SQL Group By

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