Link to home
Create AccountLog in
Avatar of al4629740
al4629740Flag for United States of America

asked on

query syntax - multiply a column by 10 into another column

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?
ASKER CERTIFIED SOLUTION
Avatar of Darrell Porter
Darrell Porter
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of al4629740

ASKER

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'
I made a mistake.  The Hour column is datatype decimal(18, 2) if that makes a difference.
Found the issue.  I had an extra parenthesis!
Glad to be of help.