Formatting SQL SELECT using SUMs returns an error 2

[JeffreyLSmith]

I've added several sum functions to the query string which is "ok" per the Data Env. designer, but VB returns an error upon execution. The error appears to be within a previously working section of the query, ala before the sum functions were added. Here is the query:

"SELECT SUM(Mon) AS MonHrs, SUM(Tue) AS TueHrs,
SUM(Wed) AS WedHrs, SUM(Thu) AS ThuHrs,
SUM(Fri) AS FriHrs, SUM(Sat) AS SatHrs,
SUM(Sun) AS SunHrs,
Project, Task,
Mon, Tue, Wed, Thu, Fri, Sat, Sun,
Purpose, Issue, WeekEnding,
EmployeeName, ID
FROM tblTimeEntries
WHERE WeekEnding = 'sWeekEnding' AND
EmployeeName = 'sCurrentUser'"

It now bombs with this error:

"you tried to execute a query which does not include the specified expression 'Project' as part of an aggregate function"

????? Again, the DE designer checked the syntax with no problems but Visual Basic is dumping? Any help?

 

[Antzz]

In the said query, you are trying to use an aggregate function. So for all the other fields that you are not using the aggregate function, you will need to use the Group by clause.
So it will look something like :
SELECT SUM(Mon) AS MonHrs, SUM(Tue) AS TueHrs,
SUM(Wed) AS WedHrs, SUM(Thu) AS ThuHrs,
SUM(Fri) AS FriHrs, SUM(Sat) AS SatHrs,
SUM(Sun) AS SunHrs,
Project, Task,
Mon, Tue, Wed, Thu, Fri, Sat, Sun,
Purpose, Issue, WeekEnding,
EmployeeName, ID
FROM tblTimeEntries
WHERE WeekEnding = 'sWeekEnding' AND
EmployeeName = 'sCurrentUser'
Group by Project, Task, Mon....... and so on...
You will need to include all the fields missing from the aggregate function.

 

[JeffreyLSmith]

Thanks for the info Antzz... I have a second question relative to the SUM function. Is the resulting field as described by the AS parameter a summation of all the records in the SUM(FIELD) field? Hope this makes sense...

Thanks.

 

[steveblum]

Jeffrey,

The Sum function returns a sum of all the values in each record for that field. It does not return the number of records. To return that you would use the Count function. For you to use Sum, the field must be a numeric field. Are Mon, Tues, ..., numeric fields? If not, maybe you are looking for a count.

I'm not sure if that helps. If not, try explaining what the datatype is of the fields you are selecting and what you need to get out of the query.

Steve