Calculate sum and skewness

akmhasan · July 19th, 2003, 10:41 PM

Dear All

How can I calculate sum and skewness using same sql statement.

Regards.

Jeff Mason · July 20th, 2003, 06:03 AM

Calculating the sum of a set of rows is easy to do using the SUM aggregate function. SQL Server has a couple of a statistical aggregate functions which will calculate the standard deviation or variance of a column value in a set of rows. As I understand skewness. there are many different definitions of skewness depending on the distribution of the data. Some of these calculations are easy, some are quite difficult (involving integrals). Such calculations are beyond the scope of a database management system and a data manipulation language like SQL.

You should do such calculations in your client.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

akmhasan · July 21st, 2003, 04:25 AM

I want to calculate sum(fieldname) and the summation of (fieldname-AVG(fieldname))/STDEV(fieldname). How I can do that using same SQL statement.

Quote:

quote:Originally posted by Jeff Mason
Calculating the sum of a set of rows is easy to do using the SUM aggregate function. SQL Server has a couple of a statistical aggregate functions which will calculate the standard deviation or variance of a column value in a set of rows. As I understand skewness. there are many different definitions of skewness depending on the distribution of the data. Some of these calculations are easy, some are quite difficult (involving integrals). Such calculations are beyond the scope of a database management system and a data manipulation language like SQL.

You should do such calculations in your client.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Jeff Mason · July 21st, 2003, 05:45 AM

You really want to do this in your client.

The only way I can see to do this is with a bunch of subqueries:

Code:

SELECT SUM(YourField), SUM(YourField-TheAve)/(SELECT STDEV(YourField) FROM YourTable)
FROM (SELECT YourField, (SELECT AVG(Amount) FROM YourTable) AS TheAve
 FROM YourTable) AS TheData

This will get hideous fast as the complexity (JOINS, WHERE clauses, etc.) of the base query increases. Not to mention probably slow. Do it in the client. :)

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com