Morioh
Login
Iliana Welch

Iliana Welch

11 months ago

5 Ways to Group MySQL Data by Multiple Columns

n 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;

I hope you get an idea about mysql group by multiple columns.

#mysql  #sql  #database 

5 Ways to Group MySQL Data by Multiple Columns
1.05 GEEK