Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 146
  • 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
 
QlemoC++ DeveloperCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help 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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

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