query syntax - multiply a column by 10 into another column

al4629740
al4629740 used Ask the Experts™
on
I need help with query syntax in SQL.  I have the following SQL statement:

select  H.Agency,Count(distinct H.ActivityDate) as [# of days],SUM(Hours) as [Total Hours], ([Total Hours] * 10) as [Check Total], r.LastName,r.FirstName,r.BirthDate " & _
"from tblCYEPHours H inner join tblCYEPRegistrations R on H.Regid = R.RegID Where h.[Hours] > 0 and h.Agency = 'Chicago Area Project' And h.ActivityDate >= '" & DTPicker1 & "' And h.ActivityDate <= '" & DTPicker2 & "' " & _
"group by H.Agency,r.LastName,r.FirstName,r.BirthDate Order by H.Agency,r.LastName,r.FirstName

Open in new window


I have an error with the [Check Total] column calculation.  I am trying to multiply the [Total Hours] by 10 for the [Check Total] column.  What am I doing wrong?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Enterprise Business Process Architect
Commented:
Try
...((Sum(Hours)) * 10) as [Check Total]... 

Open in new window

instead.
You're trying to generate a calculation from a field in the same level query that is, in and of itself, a calculated field which has not yet been queried.

Author

Commented:
Ok this is what I used:

select  H.Agency,Count(distinct H.ActivityDate) as [# of days],SUM(Hours) as [Total Hours], (((Sum(Hours)) * 10) as [Check Total], r.LastName,r.FirstName,r.BirthDate " & _
"from tblCYEPHours H inner join tblCYEPRegistrations R on H.Regid = R.RegID Where h.[Hours] > 0 and h.Agency = 'Chicago Area Project' And h.ActivityDate >= '" & DTPicker1 & "' And h.ActivityDate <= '" & DTPicker2 & "' " & _
"group by H.Agency,r.LastName,r.FirstName,r.BirthDate Order by H.Agency,r.LastName,r.FirstName

Open in new window


I get incorrect syntax near keyword 'as'

Author

Commented:
I made a mistake.  The Hour column is datatype decimal(18, 2) if that makes a difference.

Author

Commented:
Found the issue.  I had an extra parenthesis!
Darrell PorterEnterprise Business Process Architect

Commented:
Glad to be of help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial