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 multiple columns with Where
SELECT Position,
Modification,
SUM(Earning),
SUM(Maintenance)
FROM `tamilblasters`.member
WHERE Position <> 'Clerical'
GROUP BY Position, Modification;
SELECT Position,
COUNT(EmpID)
FROM `tamilblasters`.member
GROUP BY Position;
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;
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;
SELECT Position,
VARIANCE(Earning),
VAR_POP(Earning),
VAR_SAMP(Earning)
FROM `tamilblasters`.member
GROUP BY Position;
SELECT Position,
STD(Earning),
STDDEV(Earning),
STDDEV_POP(Earning),
STDDEV_SAMP(Earning)
FROM `tamilblasters`.member
GROUP BY Position;
SELECT Position,
MAX(Earning),
MAX(Maintenance)
FROM `tamilblasters`.member
GROUP BY Position;
SELECT Position,
MIN(Earning),
MIN(Maintenance)
FROM `tamilblasters`.member
GROUP BY Position;
SELECT Position,
AVG(Earning),
AVG(Maintenance)
FROM `tamilblasters`.member
GROUP BY Position;
SELECT Position,
SUM(Earning),
SUM(Maintenance)
FROM `tamilblasters`.member
GROUP BY Position;
SELECT Position,
SUM(DISTINCT Earning),
SUM(DISTINCT Maintenance)
FROM `tamilblasters`.member
GROUP BY Position;
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;
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.