What is the smallest and largest individual for Charge Hour for Job Class?

Question

I need to answer this tricky question in SQL, I tried many ways and finally got this query which retrieves only MAX SUM or MIN SUM without the respective employee.

This code retrieves only the MAX charge SUM values but I need it with MAX SUM value (group by job code) with the employee ID or Name as request in the question.

select MAX(t.SUM_CHARGE)
from
(select DISTINCT e.JOB_CODE2019,a.EMP_NUM2019,a.PROJ_NUM2019,SUM(a.ASSIGN_CHARGE) as SUM_CHARGE
     from ASSIGNMENT2019 a inner join EMPLOYEE2019 e 
     on a.EMP_NUM2019 = e.EMP_NUM2019
     inner join JOB2019 j
     on  e.JOB_CODE2019 = j.JOB_CODE2019
     group by a.EMP_NUM2019)t
group by t.JOB_CODE2019 ;

here is the table create and insert statement.

create table JOB2019(
JOB_CODE2019 int,
JOB_DESCRIPTION2019 varchar(30),
JOB_CHG_HOUR2019 decimal(4,2),
PRIMARY KEY(JOB_CODE2019 )
);

create table EMPLOYEE2019(
EMP_NUM2019 int,
EMP_LNAME2019 varchar(20),
EMP_FNAME2019 varchar(20),
EMP_INITIAL2019 varchar(1),
EMP_HIREDATE2019 varchar(50),
JOB_CODE2019 int,
primary key (EMP_NUM2019),
foreign key (JOB_CODE2019) references JOB2019(JOB_CODE2019)
);

create table PROJECT2019(
PROJ_NUM2019 int,
PROJ_NAME2019 varchar(20),
EMP_NUM2019 int,
primary key (PROJ_NUM2019),
foreign key (EMP_NUM2019) references employee2019(EMP_NUM2019)
);

create table ASSIGNMENT2019(
ASSIGN_NUM2019 int,
ASSIGN_DATE2019 varchar(50),
PROJ_NUM2019 int,
EMP_NUM2019 int,
ASSIGN_HOURS2019 decimal(5,2),
ASSIGN_CHG_HOUR2019 decimal(5,2),
ASSIGN_CHARGE2019 decimal(7,2) ,
primary key (ASSIGN_NUM2019),
foreign key (PROJ_NUM2019) references project2019(PROJ_NUM2019),
foreign key (EMP_NUM2019) references employee2019(EMP_NUM2019)
);                                                                                    

insert into JOB2019
values (500,'Programmer',35.75)
,(501,'Systems Analyst',96.75)
,(502,'Database Designer',105.00)
,(503,'Electrical Engineer',84.50)
,(504,'Mechanical Engineer',67.90)
,(505,'Civil Engineer',55.78)
,(506,'Clerical Support',26.87)
,(507,'DSS Analyst',45.95)
,(508,'Applications Designer',48.10)
,(509,'Bio Technician',34.55)
,(510,'General Support',18.36);


insert into EMPLOYEE2019
values (101,'News','John','G','08Nov2000'd,502)
,(102,'Senior','David','H','12Jun1989'd,501)
,(103,'Arbough','June','E','01Dec1997'd,503)
,(104,'Ramoras','Anne','K','15Nov1988'd,501)
,(105,'Johnson','Alice','K','01Feb1994'd,502)
,(106,'Smithfield','William',' ','22Jun2005'd,500)
,(107,'Alonzo','Maria','D','10Oct1994'd,500)
,(108,'Washington','Ralph','B','22Aug1889'd,501)
,(109,'Smith','Larry','W','18Jul1999'd,501)
,(110,'Olenko','Gerald','A','11Dec1996'd,505)
,(111,'Wabash','Geoff','B','04Apr1989'd,506)
,(112,'Smithson','Darlene','M','23Oct1995'd,507)
,(113,'Joenbrood','Delbert','K','15Nov1994'd,508)
,(114,'Jones','Annelise',' ','20Aug1991'd,508)
,(115,'Bawangi','Travis','B','25Jan1990'd,501)
,(116,'Pratt','Gerald','L','05Mar1995'd,510)
,(117,'Williamson','Angie','H','19Jun1994'd,509)
,(118,'Frommer','James','J','04Jan2006'd,510);

insert INTO PROJECT2019
values (15,'Evergreen',105)
,(18,'Amber Wave',104)
,(22,'Rolling Tide',113)
,(25,'Starflight',101); 

insert into ASSIGNMENT2019(ASSIGN_NUM2019,ASSIGN_DATE2019,PROJ_NUM2019,EMP_NUM2019,ASSIGN_HOURS2019,ASSIGN_CHG_HOUR2019)
values(1001,'04Mar2012'd,15,103,2.6,84.50)
,(1002,'04Mar2012'd,18,118,1.4,18.36)
,(1003,'05Mar2012'd,15,101,3.6,105.00)
,(1004,'05Mar2012'd,22,113,2.5,48.10)
,(1005,'05Mar2012'd,15,103,1.9,84.50)
,(1006,'05Mar2012'd,25,115,4.2,96.75)
,(1007,'05Mar2012'd,22,105,5.2,105.00)
,(1008,'05Mar2012'd,25,101,1.7,105.00)
,(1009,'05Mar2012'd,15,105,2.0,105.00)
,(1010,'06Mar2012'd,15,102,3.8,96.75)
,(1011,'06Mar2012'd,22,104,2.6,96.75)
,(1012,'06Mar2012'd,15,101,2.3,105.00)
,(1013,'06Mar2012'd,25,114,1.8,48.10)
,(1014,'06Mar2012'd,22,111,4.0,26.87)
,(1015,'06Mar2012'd,25,114,3.4,48.10)
,(1016,'06Mar2012'd,18,112,1.2,45.95)
,(1017,'06Mar2012'd,18,118,2.0,18.36)
,(1018,'06Mar2012'd,18,104,2.6,96.75)
,(1019,'06Mar2012'd,15,103,3.0,84.50)
,(1020,'07Mar2012'd,22,105,2.7,105.00)
,(1021,'08Mar2012'd,25,108,4.2,96.75)
,(1022,'07Mar2012'd,25,114,5.8,48.10)
,(1023,'07Mar2012'd,22,106,2.4,35.75);

Update assignment2019
set ASSIGN_CHARGE2019 = ASSIGN_HOURS2019 * ASSIGN_CHG_HOUR2019;

I highly appreciate your help to solve the above question. Thank you.

The word 'values' appears rather often. Did you test this code before posting? — Strawberry, May 23 '20 at 20:20
The query looks syntactically incorrect. You have multiple fields which are not aggregate, but you have only 1 group by field. — Srinika Pinnaduwage, May 23 '20 at 21:03
@nbk that's why i mentioned 'Some keywords may be different as I'm doing this with PROC SQL.' I need a general idea of doing a such thing. — Nawoda Dharmabandu, May 24 '20 at 07:54
@Strawberry can you give a general idea of doing such thing? taking sum from one table then taking max and min group by those sum by job? Your help is much appreaciated than you sir/madam. — Nawoda Dharmabandu, May 24 '20 at 07:57
First fix your code, so that it actually works. Those are NOT the table inserts — Strawberry, May 24 '20 at 08:11
@Strawberry I have removed all the proc sql keywords and corrected the codes to be compatible with MySQL now. kindly check the codes and help me to go through this question. Of course, this is a tricky question for me which I'm trying to solve for 2days Thank you. — Nawoda Dharmabandu, May 24 '20 at 09:12
FYI - this would not be an efficient SAS approach to this question. Using PROC SUMMARY would be the correct approach. https://gist.github.com/statgeek/25c614fafe1316a2da1fab830036bb5c — Reeza, May 24 '20 at 19:09

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

You want help, so you also have to make the effort to present a mre see Why should I provide a Minimal Reproducible Example for a very simple SQL query?

This what you want gives a very ugly query

SELECT t2.JOB_CODE2019,maxcharge,MIN(CONCAT(t1.EMP_FNAME2019,' ' ,t1.EMP_LNAME2019)) empnamemax,mincharge,
MIN(CONCAT(t3.EMP_FNAME2019,' ' ,t3.EMP_LNAME2019)) empnamemin
FROM
(select t.JOB_CODE2019,MAX(t.SUM_CHARGE) maxcharge,MIN(t.SUM_CHARGE) mincharge
from
(select DISTINCT e.JOB_CODE2019,a.EMP_NUM2019, a.PROJ_NUM2019,SUM(a.ASSIGN_CHARGE2019) as SUM_CHARGE
     from ASSIGNMENT2019 a inner join EMPLOYEE2019 e 
     on a.EMP_NUM2019 = e.EMP_NUM2019
     inner join JOB2019 j
     on  e.JOB_CODE2019 = j.JOB_CODE2019
     group by a.PROJ_NUM2019,e.JOB_CODE2019,a.EMP_NUM2019)t
group by t.JOB_CODE2019) t2
INNER JOIN (select DISTINCT e.JOB_CODE2019,a.EMP_NUM2019, EMP_LNAME2019 , EMP_FNAME2019, a.PROJ_NUM2019,SUM(a.ASSIGN_CHARGE2019) as SUM_CHARGE
     from ASSIGNMENT2019 a inner join EMPLOYEE2019 e 
     on a.EMP_NUM2019 = e.EMP_NUM2019
     inner join JOB2019 j
     on  e.JOB_CODE2019 = j.JOB_CODE2019
     group by a.PROJ_NUM2019,e.JOB_CODE2019,a.EMP_NUM2019, EMP_LNAME2019 , EMP_FNAME2019)t1 
     ON t2.JOB_CODE2019 = t1.JOB_CODE2019 AND t2.maxcharge = t1.SUM_CHARGE
INNER JOIN (select DISTINCT e.JOB_CODE2019,a.EMP_NUM2019, EMP_LNAME2019 , EMP_FNAME2019, a.PROJ_NUM2019,SUM(a.ASSIGN_CHARGE2019) as SUM_CHARGE
     from ASSIGNMENT2019 a inner join EMPLOYEE2019 e 
     on a.EMP_NUM2019 = e.EMP_NUM2019
     inner join JOB2019 j
     on  e.JOB_CODE2019 = j.JOB_CODE2019
     group by a.PROJ_NUM2019,e.JOB_CODE2019,a.EMP_NUM2019, EMP_LNAME2019 , EMP_FNAME2019)t3 
     ON t2.JOB_CODE2019 = t3.JOB_CODE2019 AND t2.mincharge = t3.SUM_CHARGE     
 GROUP BY t2.JOB_CODE2019;

JOB_CODE2019 | maxcharge | empnamemax         | mincharge | empnamemin        
-----------: | --------: | :----------------- | --------: | :-----------------
         503 |    633.75 | June Arbough       |    633.75 | June Arbough      
         510 |     62.42 | James Frommer      |     62.42 | James Frommer     
         501 |    406.35 | Ralph Washington   |    251.55 | Anne Ramoras      
         502 |    829.50 | Alice Johnson      |    178.50 | John News         
         508 |    529.10 | Annelise Jones     |    120.25 | elbert Joenbrood  
         506 |    107.48 | Geoff Wabash       |    107.48 | Geoff Wabash      
         507 |     55.14 | arlene Smithson    |     55.14 | arlene Smithson   
         500 |     85.80 | William Smithfield |     85.80 | William Smithfield

db<>fiddle here

Dear Sir/Madam, Thank you very much for your help and advice I was struggling for two 2days. Here maxcharge and mincharge charge is same, can you explain it to me please? — Nawoda Dharmabandu, May 24 '20 at 14:17
in the linked example is your inner SELECT written out, so that you can check the project against the employees in clear text without agrregation, as you can see for job number 500 is only one entry so max is equal to min. That is also true for the other where they are equal. If you expect other data, check the last resultset in the link and change the query — nbk, May 24 '20 at 14:22
Dear Sir/Mam,Under 502, Alice Johnson earned 1039.50 and it is the max. but this gives the value wrong. other all the values are correct. I'm trying to fix it and I'm confused because your method seems correct. — Nawoda Dharmabandu, May 24 '20 at 17:08
No, when you do a SELECT * FROM ASSIGNMENT2019 WHERE EMP_NUM2019 = 105; after the update, you see that Alice worked for **two jobs** 15 and 22 , and so the hours are split, you group per job. that means you want the lowest and highest per job — nbk, May 24 '20 at 20:31

What is the smallest and largest individual for Charge Hour for Job Class?

1 Answers1