sql group by multiple columns – Best 10 Examples of GROUP BY

In this Article, you will learn sql group by multiple columns, SQL Query Order of Execution, SQL GROUP BY with INNER JOIN, SQL GROUP BY with ORDER BY, SQL GROUP BY with HAVING, SQL GROUP BY with MIN, MAX, and AVG, SQL GROUP BY with SUM function, SQL GROUP BY multiple columns, SQL GROUP BY and DISTINCT, how to use sql group by multiple columns to group rows based on one or more columns.

sql group by multiple columns

Syntax: SQL GROUP BY Multiple Columns

SELECT
column1, column2,..., columnm, aggregate_function(columni)
FROM
target_table
WHERE
conditions_or_constraints
GROUP BY criteriacolumn1 , criteriacolumn2,...,criteriacolumnj;

Using group by on multiple columns

Table: Article_Selection

+---------+----------+----------+
| Article | Bloggers | Attendee |
+---------+----------+----------+
| JDK001  |        1 | John     |
| JDK001  |        1 | Bob      |
| JDK001  |        1 | Mickey   |
| JDK001  |        2 | Jenny    |
| JDK001  |        2 | James    |
| DDO114  |        1 | John     |
| DDO114  |        1 | Erica    |
+---------+----------+----------+

When you use a group by on the subject column only; say:

select Article, Count(*)
from Article_Selection
group by Article

You will get something like:

+---------+-------+
| Article | Count |
+---------+-------+
| JDK001  |     5 |
| DDO114  |     2 |
+---------+-------+

because there are 5 entries for JDK001, and 2 for DDO114

If i were to group by two columns: using sql group by multiple columns having count

select Article, Bloggers, Count(*)
from Article_Selection
group by Article, Bloggers

i would get this:

Read Also:  wheredate in laravel | Eloquent date filtering : whereDate()

+---------+----------+-------+
| Article | Bloggers | Count |
+---------+----------+-------+
| JDK001  |        1 |     3 |
| JDK001  |        2 |     2 |
| DDO114  |        1 |     2 |
+---------+----------+-------+

This is because, when I group by two columns, it is saying “Group them so that all of those with the same Article and Bloggers are in the same group, and then calculate all the aggregate functions (Count, Sum, Average, etc.) for each of those groups”.

In this example, this is demonstrated by the fact that, when i count them, there are three students doing JDK001 in bloggers 1, and two doing it in bloggers 2.

Both of the students doing DDO114 are in bloggers 1, so there is no row for bloggers 2 (no data fits into the group “DDO114, Bloggers 2”)

Also Remember this order:

  • SELECT
  • FROM
  • WHERE
  • GROUP BY
  • HAVING
  • ORDER BY

Also Read: sql count distinct

Group By single column:

SELECT MAX(id) AS id,
    fruits,
    MAX(price) AS price,
    MAX(sell) AS sell,
    MAX(price-sell) AS stoc,
    MAX(productcode) AS productcode
FROM products
GROUP BY fruits
ORDER BY fruits

SQL GROUP BY multiple columns

The following “sql group by multiple columns different tables” statement groups rows with the same values in both agency_id and job_id columns in the same group then returns the rows for each of these groups.

sql group by multiple columns
sql group by multiple columns

Group By multiple columns:

SELECT 
    e.agency_id,
    agency_name,
    e.job_id,
    job_title,
    COUNT(member_id)
FROM
    members e
        INNER JOIN
    agencys d ON d.agency_id = e.agency_id
        INNER JOIN
    jobs j ON j.job_id = e.job_id
GROUP BY e.agency_id , e.job_id;

SQL GROUP BY with INNER JOIN example

To get the agency name, you join the members table with the agencys table as follows:

Read Also:  PHP Set custom headers using cURL Example

SELECT
	e.agency_id,
	agency_name,
	COUNT(member_id) event
FROM
	members e
INNER JOIN agencys d ON d.agency_id = e.agency_id
GROUP BY
	e.agency_id;

SQL GROUP BY with ORDER BY example

To sort the agencys by event, you add an ORDER BY clause as the following statement:

SELECT 
    e.agency_id,
    agency_name,
    COUNT(member_id) event
FROM
    members e
        INNER JOIN
    agencys d ON d.agency_id = e.agency_id
GROUP BY e.agency_id
ORDER BY event DESC;

SQL GROUP BY with HAVING example

To find the agency whose event is greater than 5, you use the HAVING clause as the following query:

SELECT 
    e.agency_id,
    agency_name,
    COUNT(member_id) event
FROM
    members e
        INNER JOIN
    agencys d ON d.agency_id = e.agency_id
GROUP BY e.agency_id
HAVING event > 5
ORDER BY event DESC;

SQL GROUP BY with MIN, MAX, and AVG example

The following query returns the minimum, maximum, and average earnings of members in each agency.

SELECT 
    e.agency_id,
    agency_name,
    MIN(earnings) min_earnings,
    MAX(earnings) max_earnings,
    ROUND(AVG(earnings), 2) average_earnings
FROM
    members e
        INNER JOIN
    agencys d ON d.agency_id = e.agency_id
GROUP BY e.agency_id;

SQL GROUP BY with SUM function (SQL GROUP BY multiple columns sum)

To get the total earnings per agency, you apply the SUM function to the earnings column and group members by the agency_id column as follows:

Read Also:  javascript json decode - How to Parse JSON in javascript?

SELECT 
    e.agency_id,
    agency_name,
    SUM(earnings) total_earnings
FROM
    members e
        INNER JOIN
    agencys d ON d.agency_id = e.agency_id
GROUP BY e.agency_id;

SQL GROUP BY and DISTINCT

If you use the GROUP BY clause without an aggregate function, the GROUP BY clause behaves like the DISTINCT operator.

The following gets the mobile numbers of members and also group rows by the mobile numbers.

SELECT
	mobile_number
FROM
	members
GROUP BY
	mobile_number;

SELECT DISTINCT
	mobile_number
FROM
	members;

Also Read : sql count distinct