SQL Aggregate Function Error In VB.Net
Posted on 2012-08-21
Please consider The following SQL statement:
sSQL = "Select LeaveHis.LvType, Employees.Gender, Sum(iif(Employees.Gender = 'Male',LeaveHis.LvDays,0)) As MaleCnt, Sum(iif(Employees.Gender = 'Female',LeaveHis.LvDays,0)) As FemCnt From LeaveHis, Employees Where LeaveHis.PersalNo = Employees.PersalNo And LeaveHis. LvType In ('AN', 'SK') Group By LeaveHis.LvType"
From this statement, I am hoping to get the following Output Table:
Leave Type MaleCnt FemCnt
AN 112 86
SK 205 125
When I execute the above select statement, SQL gives me the following error:
"You tried to execute a query that does not include the specified expression 'Gender'
as part of an aggregate function".
I can understand why SQL is moaning, but I don't know how to eliminate the problem since 'Gender' is in a different table (Employees) to the LeaveHis table. The two tables are obviously related by PersalNo (Employee Number).
I hope that somebody can help me modify the above select statement so I can get the desired result.