Today, We want to share with you mysql group by multiple columns.In this post we will show you sql group by multiple values, hear for sql group by multiple columns different tables we will give you demo and example for implement.In this post, we will learn about Sql Group By Multiple Columns with an example.
MySQL Group By Multiple Columns
In this example, we are going to use Multiple Columns. First, it groups the rows by each Position and then Modification. Next, this sql query finds the Sum of Earning and Total Maintenance in each group.
Example 1: mysql group by multiple columns
SELECT Position, Modification, SUM(Earning), SUM(Maintenance) FROM `tamilblasters`.member GROUP BY Position, Modification;
From the above, though, i grouped by the Position, column values are repeating. It is because i used Position and Modification columns in the group by clause.
MySQL Group By Where Clause
mysql group by multiple columns with Where
SELECT Position, Modification, SUM(Earning), SUM(Maintenance) FROM `tamilblasters`.member WHERE Position <> 'Clerical' GROUP BY Position, Modification;
MySQL Group By Count Single Column
SELECT Position, COUNT(EmpID) FROM `tamilblasters`.member GROUP BY Position;
MySQL Group By Order By Example
Here, the grouped result data is sorted by the Total Earning of each group in descending order in mysql group by multiple columns.
SELECT Position, Modification, SUM(Earning), SUM(Maintenance) FROM `tamilblasters`.member GROUP BY Position, Modification ORDER BY SUM(Earning) DESC;
Group By Aggregate Functions Example
mysql group by multiple columns with Aggregate Functions
SELECT Modification, Position, SUM(Earning), AVG(Earning), MIN(Earning), MAX(Earning), STD(Earning), VARIANCE(Earning) FROM member GROUP BY Modification, Position;
Group By Variance Example
SELECT Position, VARIANCE(Earning), VAR_POP(Earning), VAR_SAMP(Earning) FROM `tamilblasters`.member GROUP BY Position;
Group By STD Example
SELECT Position, STD(Earning), STDDEV(Earning), STDDEV_POP(Earning), STDDEV_SAMP(Earning) FROM `tamilblasters`.member GROUP BY Position;
Group By Max Example
SELECT Position, MAX(Earning), MAX(Maintenance) FROM `tamilblasters`.member GROUP BY Position;
MySQL Group By Min Example
SELECT Position, MIN(Earning), MIN(Maintenance) FROM `tamilblasters`.member GROUP BY Position;
MySQL Group By Avg Example
SELECT Position, AVG(Earning), AVG(Maintenance) FROM `tamilblasters`.member GROUP BY Position;
MySQL Group By Sum Example
SELECT Position, SUM(Earning), SUM(Maintenance) FROM `tamilblasters`.member GROUP BY Position;
Group By Distinct Sum Example
SELECT Position, SUM(DISTINCT Earning), SUM(DISTINCT Maintenance) FROM `tamilblasters`.member GROUP BY Position;
Using MySQL GROUP BY with HAVING clause example
SELECT YEAR(depositDate) AS year, SUM(quantitydeposited * priceEach) AS total FROM deposits INNER JOIN depositdetails USING (depositNumber) WHERE status = 'Shipped' GROUP BY year HAVING year > 2003;
MySQL GROUP BY with aggregate functions
Example 1
SELECT status, COUNT(*) FROM deposits GROUP BY status;
Example 2
SELECT status, SUM(quantityDeposited * priceEach) AS amount FROM deposits INNER JOIN depositdetails USING (depositNumber) GROUP BY status;
Example 3
SELECT depositNumber, SUM(quantitydeposited * priceEach) AS total FROM depositdetails GROUP BY depositNumber;
https://dev.mysql.com/doc/refman/8.0/en/group-by-modifiers.html
I hope you get an idea about mysql group by multiple columns.
I would like to have feedback on my infinityknow.com blog.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.