Solved

Select Sum(Numeric-Col-Name)

Posted on 2014-12-25
10
140 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 70

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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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

Independent Software Vendors: 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
VBScript on Html 15 65
Adding 1 the value of input "text" every 3 seconds 5 48
Help with IIS intermittent hangs on Windows 2012 5 65
Compress Newid value ms sql Mssql 4 48
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

751 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