CODEWITHSUNDEEP
mysql
Thadeuse
Thadeuse

Reputation: 1803

finding the maximum of all averaged results

I've got a table EmployeeDetails

DeptID   | EmpName |  workDate | percentage
------------------------------------------
dev         Sam       06/07/16   10%
dev         Smith     07/07/16   20%
HR          Denis     06/07/16   5%
HR          Danny     07/07/16   3%
IT          David     06/07/16   15%
IT          John      07/07/16   20%

Could you please help me to find a sql query that obtains the highest average(percentage) for each department and lowest average(percentage) for each department.

Thank you very much.

Upvotes: 2

Views: 54

Answers (3)

Piyush Gupta
Piyush Gupta

Reputation: 2179

Try this:

SELECT DeptID, EmpName, MAX(percentage),MIN(percentage)
    FROM employeedetails
    GROUP BY deptid;

Output: SEE DEMO HERE

NOTE: You should not store '%' char in database, it can be make misleading for Query.

Update 1: As per your comment in answer first You want to get Average of percent department wise then you want to get max and min value of percentage department wise. You can achieve this with help of GROUP_CONCAT and UNION functions

Try this Query:

(SELECT DeptID, 
    GROUP_CONCAT(EmpName SEPARATOR ',') AS Employees, 
    AVG(percentage) AS Max_Min_AvgValue
    FROM employeedetails 
    GROUP BY deptid 
    ORDER BY percentage DESC LIMIT 1)

    UNION

(SELECT DeptID, 
    GROUP_CONCAT(EmpName SEPARATOR ','), 
    AVG(percentage)
    FROM employeedetails 
    GROUP BY deptid 
    ORDER BY percentage ASC LIMIT 1);

Output: SEE DEMO HERE for Update1

Upvotes: 2

Ankit Agrawal
Ankit Agrawal

Reputation: 2454

select *,max(percentage),min(percentage)
from department
group by deptid

Upvotes: 0

Chamindu
Chamindu

Reputation: 716

How about this.

SELECT MAX(percentage) as maximum, MIN(percentage) as minimum
FROM EmployeeDetails
GROUP BY DeptId

Upvotes: 0

Related Questions