another .jsp question

[Homerboy]

Its chocking on the query even if I remove the "WHERE" condition. Am I not using AVG properly?

Thanks

 

[Cerebus451]

According to the docs, AVG is an aggregate function, meaning that you have to have a GROUP BY column. Add a GROUP BY p.team into your query.

EDIT: After reading it closer, it appears you don't need the GROUP BY (which means perform across all rows), so I'm not sure.

EDIT2: What specific error message is being returned?

 

[notfred]

How is that a JSP question?

 

[Homerboy]

Originally posted by: notfred
How is that a JSP question?

er... your right. It is in a .jsp file and I was just POed when I posted and didnt think straight.

 

[Homerboy]

Originally posted by: Cerebus451
According to the docs, AVG is an aggregate function, meaning that you have to have a GROUP BY column. Add a GROUP BY p.team into your query.

EDIT: After reading it closer, it appears you don't need the GROUP BY (which means perform across all rows), so I'm not sure.

EDIT2: What specific error message is being returned?

Thats the trick
there is no error msg, just the HTML is choking at that point when I add it to the .jsp file (and if I enter it into mySQLfront as a SQL query).

 

[bunker]

make sure teamid actually has a value in it.

Try running it by substituting an acutal number for the variable.

 

[Homerboy]

Originally posted by: bunker
make sure teamid actually has a value in it.

Try running it by substituting an acutal number for the variable.

Well I havent tried that, but I have tried it solely without the "teamid" varialbe in there at all at that craps out too


ResultSet teamovr = stmt.executeQuery("SELECT AVG(ovrl) AS averageovrl FROM players WHERE team = 2")
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'ResultSet teamovr = stmt.executeQuery("SELECT AVG(ovrl) AS aver

 

[torpid]

Try it without the "AS"

 

[imported_haris]

Does the query run outside of the jsp page?

If so, did you check to make sure that the DB connection is working by using a simple statement like "select * from players;"?

The only other thing that jumps out at me right now is you might need to add + ";" after the teamid.

 

[Homerboy]

well the example I see on mysql.com shows:

mysql> SELECT student_name, AVG(test_score)
-> FROM student
-> GROUP BY student_name;


so I edited:

ResultSet teamovr = stmt.executeQuery("SELECT ovrl, AVG(averageovrl) FROM players WHERE team = 2 GROUP BY ovrl");

and still errors

 

[Homerboy]

Originally posted by: haris
Does the query run outside of the jsp page?

If so, did you check to make sure that the DB connection is working by using a simple statement like "select * from players;"?

The only other thing that jumps out at me right now is you might need to add + ";" after the teamid.

it runs inside the .jsp
I've tried it with completly removing the + teamid and editing with "WHERE team = 2"

 

[imported_haris]

Originally posted by: Homerboy

Originally posted by: haris
Does the query run outside of the jsp page?

If so, did you check to make sure that the DB connection is working by using a simple statement like "select * from players;"?

The only other thing that jumps out at me right now is you might need to add + ";" after the teamid.

it runs inside the .jsp
I've tried it with completly removing the + teamid and editing with "WHERE team = 2"

Does a "simple" query like the one I mentioned run at all in the jsp page?

I also asked if you can run the query using another tool that connects to the database? Something like Oracle's SQLPlus or MS's QueryAnalyzer. I used MySQL once awhile back, and I can't remember what built in tool it has for this purpose.

 

[Homerboy]

there are other queries already running on the page with no problems (not to mention a few dozen other pages through the site that work n/p)
Ive tried entering the query (with no "teamid") directly into mySQL front and get the error I posted above:

ResultSet teamovr = stmt.executeQuery("SELECT AVG(ovrl) AS averageovrl FROM players WHERE team = 2")
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'ResultSet teamovr = stmt.executeQuery("SELECT AVG(ovrl) AS aver

 

[Homerboy]

Hmmm some bits of interest here:
http://dev.mysql.com/doc/maxdb...a0c9449261/content.htm

Just for giggles I switched to SUM:

ResultSet teamovr = stmt.executeQuery("SELECT SUM(ovrl) averageovrl FROM players p WHERE p.team=" + teamid);

and it worked perfectly fine

 

[bunker]

You'll notice the example from that link does not use AS, get rid of AS like Torpid said.

 

[Homerboy]

I did try getting rid of the "AS" and it still crapped.
I can actually just use the stupid "SUM" and simple divide it by 12 (all of them should have 12 variables...if not tough shit). Its not the right way to do it, but since I can't make it work the right way Ill do it any way that works.

 

[kamper]

Originally posted by: Homerboy
there are other queries already running on the page with no problems (not to mention a few dozen other pages through the site that work n/p)
Ive tried entering the query (with no "teamid") directly into mySQL front and get the error I posted above:

ResultSet teamovr = stmt.executeQuery("SELECT AVG(ovrl) AS averageovrl FROM players WHERE team = 2")
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'ResultSet teamovr = stmt.executeQuery("SELECT AVG(ovrl) AS aver

The fact that the error message includes java code instead of simply the contents of the string seems really odd. Could you post the stack trace and the section of code surrounding this call?

I'd also recommend taking it to the command line. Whenever there's a query that doesn't work the quickest thing is to get it in a place where you can execute it over and over again quickly. What version of mysql are you running? I have 4.1.3b here and a similar query runs fine.

 

[imported_haris]

Have you checked to make sure that you don't have any data problems, like null values?

 

[Jack Ryan]

You mentioned other queries are running on this page, are you reusing the stmt or are you creating a new one for each call?



Whoops, should read more, I just read you said SUM worked fine.