Select Sum(Numeric-Col-Name)
Hi All (Merry Christmas)
I am trying to load an array in Classic asp as follows:
strSQL = "SELECT HS_ID, " &_
"HS_Stu_ID, " &_
"SUM(HS_Score) " &_
"FROM tbl_HomwWork_Scores WHERE HS_Week_ID <= 13 AND HS_Class_ID = " & NumClassID & " Order By HS_Score DESC;"
Set rsData = MyConn.Execute(strSQL)
If Not rsData.EOF Then
ArrTermOneScores = rsData.GetRows()
rsData.Close
End If
Getting an error 'Microsoft JET Database Engine error '80040e21'
Its in Spanish (Ha intentado ejecutar una consulta que no incluye la expresión especificada 'HS_ID' como parte de una función de agregado. )
Any ideas would be appreciated. I do not get the error if I remove the Sum() and just call the col.
I shall be back later.
Kind regards
I am trying to load an array in Classic asp as follows:
strSQL = "SELECT HS_ID, " &_
"HS_Stu_ID, " &_
"SUM(HS_Score) " &_
"FROM tbl_HomwWork_Scores WHERE HS_Week_ID <= 13 AND HS_Class_ID = " & NumClassID & " Order By HS_Score DESC;"
Set rsData = MyConn.Execute(strSQL)
If Not rsData.EOF Then
ArrTermOneScores = rsData.GetRows()
rsData.Close
End If
Getting an error 'Microsoft JET Database Engine error '80040e21'
Its in Spanish (Ha intentado ejecutar una consulta que no incluye la expresión especificada 'HS_ID' como parte de una función de agregado. )
Any ideas would be appreciated. I do not get the error if I remove the Sum() and just call the col.
I shall be back later.
Kind regards
Last Comment
You need to group by any column not contained in a aggregate function like SUM.
Insert the following before ORDER BY:
Insert the following before ORDER BY:
group by HS_ID, HS_Stu_ID
ASKER
Ok, that makes sense. ;-)
I now have this
strSQL = "SELECT SUM(HS_Score) Group by HS_Stu_ID " &_
"FROM tbl_HomwWork_Scores WHERE HS_Week_ID <= 13 AND HS_Class_ID = " & NumClassID & " Order By HS_Score DESC;"
Set rsData = MyConn.Execute(strSQL)
If Not rsData.EOF Then
ArrTermOneScores = rsData.GetRows()
rsData.Close
End If
Getting this error:
Microsoft JET Database Engine error '80040e14'
Error de sintaxis (falta operador) en la expresión de consulta 'SUM(HS_Score) Group by HS_Stu_ID'.
I now have this
strSQL = "SELECT SUM(HS_Score) Group by HS_Stu_ID " &_
"FROM tbl_HomwWork_Scores WHERE HS_Week_ID <= 13 AND HS_Class_ID = " & NumClassID & " Order By HS_Score DESC;"
Set rsData = MyConn.Execute(strSQL)
If Not rsData.EOF Then
ArrTermOneScores = rsData.GetRows()
rsData.Close
End If
Getting this error:
Microsoft JET Database Engine error '80040e14'
Error de sintaxis (falta operador) en la expresión de consulta 'SUM(HS_Score) Group by HS_Stu_ID'.
strSQL = "SELECT SUM(HS_Score), HS_Stu_ID " &_
"FROM tbl_HomwWork_Scores WHERE HS_Week_ID <= 13 AND HS_Class_ID = " & NumClassID & " GROUP BY HS_Stu_ID Order By HS_Score DESC;"
ASKER
Hi Gary, I really appreciate your help. I tried your code and I get this error.
Microsoft JET Database Engine error '80040e21'
Ha intentado ejecutar una consulta que no incluye la expresión especificada 'HS_Score' como parte de una función de agregado.
PS. This is running against an Access DB
Kind regards
Microsoft JET Database Engine error '80040e21'
Ha intentado ejecutar una consulta que no incluye la expresión especificada 'HS_Score' como parte de una función de agregado.
PS. This is running against an Access DB
Kind regards
ASKER
strSQL = "SELECT SUM(HS_Score) as total_score, HS_Stu_ID " &_
"FROM tbl_HomwWork_Scores WHERE HS_Week_ID <= 13 AND HS_Class_ID = " & NumClassID & " GROUP BY HS_Stu_ID Order By total_score DESC;"
ASKER
Almost !
If I run as you gave it, it throws an error:
Microsoft JET Database Engine error '80040e10'
No se han especificado valores para algunos de los parámetros requeridos.
However, if I remove the 'Order By total_score DESC' it then works. ;-(
I do need the array to be ordered by the scores...
A
If I run as you gave it, it throws an error:
Microsoft JET Database Engine error '80040e10'
No se han especificado valores para algunos de los parámetros requeridos.
However, if I remove the 'Order By total_score DESC' it then works. ;-(
I do need the array to be ordered by the scores...
A
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
That worked. Many Thanks.
A
A
ASP
Active Server Pages (ASP) is Microsoft’s first server-side engine for dynamic web pages. ASP’s support of the Component Object Model (COM) enables it to access and use compiled libraries such as DLLs. It has been superseded by ASP.NET, but will be supported by Internet Information Services (IIS) through at least 2022.
82K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
I assume you would want to be grouping by HS_Stu_ID