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:
+---------+----------+-------+ | 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.
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:
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:
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