finding the average of SQL results

busby

I hope this is a simple problem that i cant solve and someone here will know.

I need to create an SQL report showing average sales figures for a period of time decided by the user. for example if £100 was made in 10 days the average figure would be £10 a day.

Ive created a page where a user enters in 2 dates. SQL then selects the nett_value, date, order numbers etc from an orders table where the dates are between the 2 dates given.

I need to find the average nett_value from all those returned. I know i have to add together all the nett prices and then divide by how many days there are but i dont know how to do that in the code. Because if there are several purchases in 1 day i add all the purchases together but only count the day once. or miss the day completely if there were zero purchases.

So i used the SELECT SUM(nett_value) function for selectin all of the figures and adding them together...now i need to know how to divide them by how many days there are between the 2 entered dates.

can someone help me?

johanafm

Use the appropriate aggregate funtion instead. The link is to the MySql 5.5 reference, but the aggregate functions are part of the SQL spec, so whatever DBMS you use doesn't really matter.

busby

johanafm;10987444 wrote:
Use the appropriate aggregate funtion instead. The link is to the MySql 5.5 reference, but the aggregate functions are part of the SQL spec, so whatever DBMS you use doesn't really matter.

i dont really know what you mean. im using MySQL. and i thought the "SUM()" function was a aggregate function? i dont know any others

johanafm

The link I provided show you all of the available aggregate functions, sum being one of them. Another one, and also the first one listed on that page... is AVG