0

I want to do sql min and then do a division but the error appears "you tried to execute a query that does not include specified expression 'EMPID' as part of an aggregate function"

Thanks

SELECT tblEmployeeCriteria.EMPID,tblEmployee.NAME,
tblEmployeeCriteria.SKILL/MIN(tblEmployeeCriteria.SKILL)*100 
FROM tblEmployeeCriteria 
INNER JOIN tblemployee ON tblEmployeeCriteria.EMPID = tblemployee.EMPID

Database :

tblemployee

EMPID NAME
10037 Tamendran
10046 Ponisan
10085 Vanu

tblEmployeeCriteria

EMPID SKILL
10037 90
10046 80
10085 50

Desired Result :

EMPID NAME SKILL
10037 Tamendran 180
10046 Ponisan 170
10085 Vanu 100

EXAMPLE VALUE SKILL FROM TAMENDRAN : 90/50*100=180

June7
  • 19,874
  • 8
  • 24
  • 34
roy
  • 693
  • 2
  • 11
  • Either needs to be used in aggregate function or in GROUP BY clause. `GROUP BY tblEmployeeCriteria.EMPID,tblEmployee.NAME`. Then the SQL will likely fail on the SKIll division. Probably need a subquery for the aggregation. – June7 May 09 '23 at 02:35
  • @June7 ,Thank you for your feedback, `"SELECT tblEmployeeCriteria.EMPID,tblEmployee.NAME,tblEmployeeCriteria.SKILL/MIN(tblEmployeeCriteria.SKILL)*100 FROM tblEmployeeCriteria INNER JOIN tblemployee ON tblEmployeeCriteria.EMPID = tblemployee.EMPID GROUP BY tblEmployeeCriteria.EMPID,tblEmployee.NAME"` I tried according to your recommendation but still error – roy May 09 '23 at 02:47

1 Answers1

1

You use the tblEmployeeCriteria in two separate roles: 1.to get the minimum SKILL value of the entire table and 2.to get the SKILL value for each row and compare with that minimum. You can achieve this by using this table twice; one example is to use a subquery to get the min SKILL value:

SELECT 
   EC.EMPID
 , E.NME
 , EC.SKILL/(select MIN(MINSKILL.SKILL) from tblEmployeeCriteria MINSKILL)*100 as SKILL
FROM 
  tblEmployeeCriteria EC

  INNER JOIN tblemployee E
  ON EC.EMPID = E.EMPID

You could also JOIN to the subquery but this is a CROSS JOIN MsAccess does not support as a keyword, so it becomes a bit weird (a mixture of modern and out-of-date FROM-JOIN syntax:

SELECT 
   EC.EMPID
 , E.NME
 , EC.SKILL/EC2.MINSKILL*100 as SKILL
FROM 
(  tblEmployeeCriteria EC
  INNER JOIN 
  tblemployee E
  ON EC.EMPID = E.EMPID
),(select MIN(SKILL) as MINSKILL from tblEmployeeCriteria) EC2
tinazmu
  • 3,880
  • 2
  • 7
  • 20
  • Thank you very much , both Your answers went very perfectly and I liked the first answer – roy May 09 '23 at 04:15
  • Then choose as accepted answer! – tinazmu May 09 '23 at 04:35
  • Okay, I have a new post link that comes from your answer maybe you can help . [link](https://stackoverflow.com/questions/76205985/how-to-use-sql-multiplication-from-other-table-ms-access-in-vb-net) – roy May 09 '23 at 05:03