avg, sum, or stddev first n records

[mattgoody]

is it possible to sum, avg, and stddev the first n records of a table?
if i have the values:
5
7
2
5
8
9
in a table and i just want to sum the first three: 5+7+2. is there a way to do this using mysql commands?

-Matt

[chrismartz]

Give this a trySELECT TOP 3 SUM(column_name) FROM table_name GROUP BY column_name ORDER BY column_name DESCI think that is right but I'm not at a server that I can give it a try on.

[russell]

thats right...depending on dbms

[chazzy]

won't work in mysql.

to do it in mysql, you'll need to do this:

select (aggregate function list) from
(select (column list) from your_table [WHERE where clause] LIMIT 0,n)
etc..

[mattgoody]

right on chazzy, im sure thats exactly what i need to do, the only thing im confused about is exactly what i need to put after the select clause in the ()'s (is that called a subquery?)

ive written:

SELECT sum(PTS) FROM (SELECT PTS FROM Dec25 ORDER BY PTS DESC LIMIT 0,3)

i dont really know what to write next. do i need to say FROM Dec25 again? or what? im befuddled.

-Matt

EDIT: Oh, and if i just leave my query as it is in the quote, it says Every derived table must have its own alias .

[chazzy]

yes, you're right, depending on version.

that means that you would do


SELECT sum(PTS) FROM (SELECT PTS FROM Dec25 ORDER BY PTS DESC LIMIT 0,3) ptscore


You don't have to do anything else, unless you want to select more data, which you might want to.

[mattgoody]

thanks so much chazzy, that works im just curious as to why i need that last word, and what it refers to? it doesnt really matter, im just curious and would like to learn

again, thanks a ton

-Matt

[chrismartz]

Oh mysql. How different we are :P

[chazzy]

matt-
the last word is your alias. an alias is an alternate reference for something.

chris-
not really. remember, sql's only a standard. each implementation can vary.

[chrismartz]

It certainly would be nice if SQL was a standardized language throughout all db's.

[russell]

well, there is a standard (http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt) of course...but no one follows it exactly.

here is more reading on the subject (sorry for pirating your thread matt) :)
http://builder.com.com/5100-6388-1046268.html
http://www.ncb.ernet.in/education/modules/dbms/SQL99/

of course ANSI (http://www.ansi.org/) sells their standards, rather than publishes RFCs...

[chazzy]

and don't forget, none of the databases out there today actually conform to the definition of an RDBMS, no matter how many of them actually claim to be relational :)