Solved

Select Sum(Numeric-Col-Name)

Posted on 2014-12-25
10
137 Views
Last Modified: 2014-12-25
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
Comment
Question by:Abiel de Groot
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 58

Expert Comment

by:Gary
ID: 40517564
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
 
LVL 69

Expert Comment

by:Qlemo
ID: 40517568
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
 
LVL 5

Author Comment

by:Abiel de Groot
ID: 40517899
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 58

Expert Comment

by:Gary
ID: 40517913
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
 
LVL 5

Author Comment

by:Abiel de Groot
ID: 40517917
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
 
LVL 5

Author Comment

by:Abiel de Groot
ID: 40517921
This is the design view of the table.

A
EE-01.jpg
0
 
LVL 58

Expert Comment

by:Gary
ID: 40517923
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
 
LVL 5

Author Comment

by:Abiel de Groot
ID: 40517931
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
 
LVL 58

Accepted Solution

by:
Gary earned 500 total points
ID: 40517938
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
 
LVL 5

Author Comment

by:Abiel de Groot
ID: 40517951
That worked. Many Thanks.

A
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Time Conversions...both ways 2 24
Save ms data to server side. 19 59
Jquery Check against database on select from auto fill not working in all browsers 5 49
SQL to JSON 14 35
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question