returning the maximum value from a SUM query

[james0816]

should be an easy ? for someone out here (not me obviously)

current query:

select sum(b) from db where dc=1 group by ba

returns:
510
764

I would like to just capture the MAX of this query.

possible?

 

[Targol]

select max(sum(b)) from db where dc=1 group by ba" ???


Water is not bad as long as it remains outside human body ;-)

 

[james0816]

tried that...get a message saying "Invalid use of group function".

 

[Targol]

what about "select max(select sum(b) from db where dc=1 group by ba) from dual" ?
... where dual is a virtual table name used with Oracle... don't know if it exist in MySql

Water is not bad as long as it remains outside human body ;-)

 

[james0816]

not familiar with dual...but it didnt' work any way.

i have found a work around but i know there has to be an easier way. what i have currently is my query sorted on that field and then putting that data as a variable.

then...re-write the query to sort on the next field and so on.

lot of coding doing it that way...ick!

 

[Targol]

after having asked here, it seems that "Dual" table is Oracle only. Did you try with an existing table name instead : ""select max(select sum(b) from db where dc=1 group by ba) from db" ?

Water is not bad as long as it remains outside human body ;-)

 

[james0816]

yep, one of the many different variations i have tried.

 

[Targol]

what is the error message ?

Water is not bad as long as it remains outside human body ;-)

 

[james0816]

it just tells me "you have an error in your sql syntax

 

[Targol]

Don't you have a command line type tool to test your requests that should give you a more interseting error. mySql SQL command line tool for example ?

Water is not bad as long as it remains outside human body ;-)

 

[james0816]

i am using mysql control center. here is the complete line returned:

[DataBase1] ERROR 1064: 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 'select sum(b) from db where dc=1 group by ba) from db

 

[Targol]

If your mySql version is lower than 4.1, have a look at this page :

http://aspn.activestate.com/ASPN/Mail/Message/php-db/2537200

... I keep searching...

Water is not bad as long as it remains outside human body ;-)

 

[Targol]

Again for 4.o and under versions of MySql :

http://mysqld.active-venture.com/Rewriting_subqueries.html

Water is not bad as long as it remains outside human body ;-)

 

[james0816]

man i hate the thought of upgrading after all the work i put into this thing. is it a relatively simple process?

 

[Targol]

When I did that, it was quite simple : make a dump of your DB, install new version and then reimport the dump inside the new db.
Beware that protocols and connect strings may differ from one version and other.

By the way, got to go (6PM in France : work finished).
See you tomorow morning.

Water is not bad as long as it remains outside human body ;-)