?
Solved

SQL Aggregate Function Error In VB.Net

Posted on 2012-08-21
5
Medium Priority
?
758 Views
Last Modified: 2012-08-21
Hi

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.

Many thanks.
0
Comment
Question by:Nolanc
  • 3
5 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 600 total points
ID: 38315223
add the field Gender to the group by:

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, Employees.Gender" 

Open in new window

0
 

Author Comment

by:Nolanc
ID: 38315279
Hi angelIII

If I do that, will I not land up with the following table:

Leave Type   Gender   MaleCnt   FemCnt
     AN               Male       112           0                               (A)
     AN             Female       0            86
     SK                Male       205           0
     SK              Female       0           125

The reason I ask is because in my application,  I expect Column (01) in (A) above to contain a numeric value which will not be true in the above case.

I hope I am making myself clear.

Thanks
0
 

Author Comment

by:Nolanc
ID: 38315356
Hi angelIII

I just verified what I was fearing. I cannot have 'AN' for example, repeating itself.

Thanks
0
 
LVL 2

Accepted Solution

by:
kristof1104 earned 1400 total points
ID: 38315381
how about something like this?
sSQL = "Select LeaveHis.LvType, 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"

Open in new window


if you still get the aggregation error try
sSQL = "Select LeaveHis.LvType, 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,MaleCnt,FemCnt "

Open in new window

0
 

Author Comment

by:Nolanc
ID: 38315401
Hi kristof1104

Based on my original question, I can see what I have done wrong. Thank you for an excellent solution. I will be closing the question and awarding you the points.

Thank you
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question