How to get multiple results for the same aggregate function using a single SQL aggregate function query?

user_15352086 0
Viewed 130

I'm new to SQL and I've been trying around with creating views for my database. When I introduced aggregate functions I quickly stumbled upon this problem;

So in my database there are two tables: a table for user/employee data and one with groups (e.g. 'Accounting', 'Support' etc.).

I want to use a query/view to return an entire employee entry per group for the employee in that group with the min/max salary (of the group).

Here are the tables:

--- employee data ---

CREATE TABLE `db_java-sql-hookup`.`tbl_employee-data` (
    `ID_Employee` bigint UNIQUE NOT NULL AUTO_INCREMENT,
    `fk_IDGroup` tinyint,
    `EmployeeName` varchar(30) UNIQUE NOT NULL,
    `Email` varchar(255) NOT NULL,
    `Password` varchar(255) NOT NULL,
    `Salary` int,
    PRIMARY KEY (`ID_Employee`),
    FOREIGN KEY (`fk_IDGroup`) REFERENCES `db_java-sql-hookup`.`tbl_groups`(`pk_IDGroup`)
);

INSERT INTO `db_java-sql-hookup`.`tbl_employee-data` (`EmployeeName`, `Email`, `Password`)
VALUES
("TestA", "TestA@web.com", "1234"),
("TestB", "TestB@web.com", "1234"),
("TestC", "TestC@web.com", "abcde"),
("TestD", "TestD@web.com", "0000"),
("TestE", "TestE@web.com", "g8t3");
### Set Groups ###
UPDATE `db_java-sql-hookup`.`tbl_employee-data`
    SET `fk_IDGroup` = 1 WHERE `tbl_employee-data`.`ID_Employee` = 1; 
UPDATE `db_java-sql-hookup`.`tbl_employee-data`
    SET `fk_IDGroup` = 1 WHERE `tbl_employee-data`.`ID_Employee` = 2;
UPDATE `db_java-sql-hookup`.`tbl_employee-data`
    SET `fk_IDGroup` = 1 WHERE `tbl_employee-data`.`ID_Employee` = 3;
UPDATE `db_java-sql-hookup`.`tbl_employee-data`
    SET `fk_IDGroup` = 2 WHERE `tbl_employee-data`.`ID_Employee` = 4;
UPDATE `db_java-sql-hookup`.`tbl_employee-data`
    SET `fk_IDGroup` = 2 WHERE `tbl_employee-data`.`ID_Employee` = 5;
### Set Salaries ###
UPDATE `db_java-sql-hookup`.`tbl_employee-data`
    SET `Salary` = 1000 WHERE `tbl_employee-data`.`ID_Employee` = 1; 
UPDATE `db_java-sql-hookup`.`tbl_employee-data`
    SET `Salary` = 500 WHERE `tbl_employee-data`.`ID_Employee` = 2;
UPDATE `db_java-sql-hookup`.`tbl_employee-data`
    SET `Salary` = 2000 WHERE `tbl_employee-data`.`ID_Employee` = 3;
UPDATE `db_java-sql-hookup`.`tbl_employee-data`
    SET `Salary` = 750 WHERE `tbl_employee-data`.`ID_Employee` = 4;
UPDATE `db_java-sql-hookup`.`tbl_employee-data`
    SET `Salary` = 300 WHERE `tbl_employee-data`.`ID_Employee` = 5;

--- group data ---

CREATE TABLE `db_java-sql-hookup`.`tbl_groups` (
    `pk_IDGroup` tinyint UNIQUE NOT NULL AUTO_INCREMENT,
    `GroupName` varchar(40) NOT NULL,
    PRIMARY KEY (`pk_IDGroup`)
);

INSERT INTO `db_java-sql-hookup`.`tbl_groups` (`GroupName`)
VALUES 
    ("Accounting"),
    ("Support"),
    ("Development"),
    ("Test");

Prior, I worked on these views (which I would like to combine):

### MaxSalaryEmp ###    
DROP VIEW IF EXISTS `db_java-sql-hookup`.`view_MaxSalaryEmployee`;
CREATE VIEW `db_java-sql-hookup`.`view_MaxSalaryEmployee` AS    
    SELECT `ID_Employee`, `EmployeeName`, `Salary`
    FROM `db_java-sql-hookup`.`tbl_employee-data`
    WHERE `Salary` = 
        (SELECT MAX(`Salary`) FROM `db_java-sql-hookup`.`tbl_employee-data`);

### Avg,Min,Max Group Salary ###
DROP VIEW IF EXISTS `db_java-sql-hookup`.`view_CombinedGroupSalary`;
CREATE VIEW `db_java-sql-hookup`.`view_CombinedGroupSalary` AS   
    SELECT `GroupName`, 
        AVG(`Salary`) AS `AvgSalary`,
        MIN(`Salary`) AS `MinSalary`,
        MAX(`Salary`) AS `MaxSalary`
    FROM `db_java-sql-hookup`.`tbl_groups` AS grp
    LEFT JOIN `db_java-sql-hookup`.`tbl_employee-data` AS emp
    ON grp.`pk_IDGroup` = emp.`fk_IDGroup`
    GROUP BY `GroupName`
    ORDER BY `GroupName`;

I've tried something like this:

SELECT `GroupName`, `ID_Employee`, `EmployeeName`,  `Salary`,
    MAX(`Salary`) AS `MaxSalary`
FROM `db_java-sql-hookup`.`tbl_groups` AS grp
LEFT JOIN `db_java-sql-hookup`.`tbl_employee-data` AS emp
ON grp.`pk_IDGroup` = emp.`fk_IDGroup`
GROUP BY `GroupName`
ORDER BY `GroupName`;

I want the final result to look like this: https://i.stack.imgur.com/xsmLT.png

(except, it should give out the proper employees instead of whatever is happening here)

Thank you in advance!

aggregate-functions sql
2 Answers

It's unclear to me what you are trying to accomplish. Do you just want the top employees per group or do you want to list all the employees by group and show the max salary for each group?

If you want one entry per group use your combined salary view like this:

SELECT grp.`GroupName`, emp.`ID_Employee`, emp.`EmployeeName`,  emp.`Salary`,
   grp.`MaxSalary` 
FROM  `db_java-sql-hookup`.`view_CombinedGroupSalary` grp
LEFT JOIN `db_java-sql-hookup`.`tbl_employee-data` AS emp
ON grp.`pk_IDGroup` = emp.`fk_IDGroup` and grp.`MaxSalary` = emp.`Salary`
ORDER BY `GroupName`;

If you need to list all the employees do this:

SELECT grp.`GroupName`, emp.`ID_Employee`, emp.`EmployeeName`,  emp.`Salary`,
   cs.`MaxSalary`
FROM `db_java-sql-hookup`.`tbl_groups grp`
INNER JOIN `db_java-sql-hookup`.`view_CombinedGroupSalary cs 
on grp.`pk_IDGroup` = cs.`pk_IDGroup`
LEFT JOIN `db_java-sql-hookup`.`tbl_employee_data` AS emp
ON grp.`pk_IDGroup` = emp.`fk_IDGroup`
ORDER BY grp.`GroupName`, emp.`EmployeeName`;

To get these queries to work you need to include the pk_IDGroup column in your combined salary view:

DROP VIEW IF EXISTS `db_java-sql-hookup`.`view_CombinedGroupSalary`;
CREATE VIEW `db_java-sql-hookup`.`view_CombinedGroupSalary` AS   
    SELECT `pk_IDGroup`,
        `GroupName`, 
        AVG(`Salary`) AS `AvgSalary`,
        MIN(`Salary`) AS `MinSalary`,
        MAX(`Salary`) AS `MaxSalary`
    FROM `db_java-sql-hookup`.`tbl_groups` AS grp
    LEFT JOIN `db_java-sql-hookup`.`tbl_employee-data` AS emp
    ON grp.`pk_IDGroup` = emp.`fk_IDGroup`
    GROUP BY `pk_IDGroup`,`GroupName`
    ORDER BY `GroupName`;
user_149860780

For this problem I don't think that you need to use group by, instead you need to use "user-defined-variables":

This link can show you how to get max by partition in mysql (in mssql it works differently).

This is solution for your problem (sqlfiddle) in the sql fiddle i needed to remove the "db_java-sql-hookup." from the tables so you can use the code below:

select `GroupName`, `ID_Employee`, `EmployeeName`,  `Salary`,
       @max:=IF(@custtype!=`GroupName`,`Salary`,@max),
       @custtype:=`GroupName`
FROM (
  select `GroupName`, `ID_Employee`, `EmployeeName`,  `Salary`
  FROM `db_java-sql-hookup`.`tbl_groups` AS grp
  LEFT JOIN `db_java-sql-hookup`.`tbl_employee-data` AS emp
  ON grp.`pk_IDGroup` = emp.`fk_IDGroup`
  order by `GroupName`, `Salary` desc
) a, (select @max:=0, @custtype:='') t

If you need more details on how it works let me know and i will add it.

user_22187130
Related