Posted inMysql / Mysqli / php

MySQL group by multiple columns – BEST 5 WAYS TO “mysql group by multiple columns”

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 multiple columns
mysql group by multiple columns

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.

I am Jaydeep Gondaliya , a software engineer, the founder and the person running Pakainfo. I'm a full-stack developer, entrepreneur and owner of Pakainfo.com. I live in India and I love to write tutorials and tips that can help to other artisan, a Passionate Blogger, who love to share the informative content on PHP, JavaScript, jQuery, Laravel, CodeIgniter, VueJS, AngularJS and Bootstrap from the early stage.

Leave a Reply

Your email address will not be published. Required fields are marked *

We accept paid guest Posting on our Site : Guest Post Chat with Us On Skype