• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 150
  • Last Modified:

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
0
Abiel de Groot
Asked:
Abiel de Groot
  • 5
  • 4
1 Solution
 
GaryCommented:
If you are doing a SUM you need to have a GROUP BY - on some common value otherwise how is it to know which rows to sum
I assume you would want to be grouping by HS_Stu_ID
0
 
QlemoDeveloperCommented:
You need to group by any column not contained in a aggregate function like SUM.
Insert the following before ORDER BY:
group by HS_ID, HS_Stu_ID

Open in new window

0
 
Abiel de GrootAuthor Commented:
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'.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
GaryCommented:
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;"

Open in new window

0
 
Abiel de GrootAuthor Commented:
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
0
 
Abiel de GrootAuthor Commented:
This is the design view of the table.

A
EE-01.jpg
0
 
GaryCommented:
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;"

Open in new window

0
 
Abiel de GrootAuthor Commented:
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
0
 
GaryCommented:
MySQL is my db of choice, haven't used Access in years and it seems a bit weird with these type of scenarios

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 SUM(HS_Score) DESC;"

Open in new window

0
 
Abiel de GrootAuthor Commented:
That worked. Many Thanks.

A
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now