finding the average of SQL results

[busby]

Hi

 

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 dates there are but i dont know how to do that in the code. Because if there are several purchases in 1 day all of the purchases are counted but the day only gets counted 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?

[Muddy_Funster]

you could just use

 SELECT AVG(nett_value)....

[busby]

pardon my inadequate maths skills but would that get the same results as adding all the values together and dividing by the amount of distinct days there are?

[fenway]

Show us your query

[busby]

The SQL is

 

	$sql  = 'SELECT order_id, order_num, date, SUM(nett_price) ';
	$sql .= 'FROM orders ';
	$sql .= 'WHERE complete = "Y" AND tx_type = "PAYMENT" AND date >= "'.$_POST['start_date'].'" AND date <= "'.$_POST['finish_date'].'" ';

 

Lets assume i test it and type in 2 dates and it returns ten results.

 

thats 10 orders. however there is only 7 days because 3 orders were made in 1 day. so i need to add all the 10 nett_prices together and divide by the 7 days.

 

if that makes sense.

 

but i dont know how to do that in the code.

[busby]

One thing i thought of but wasnt sure if it was correct was.....

 

Could i just do another SQL statement underneath the one i already have only selecting the DISTINCT dates within that date range?

 

Then dividing the SUM of the nett values by the amount of dates returned from the second SQL statement?

 

would that work?

 

Any help is greatly appreciated.

[Muddy_Funster]

howas about keeping it all together?:

 

        	$sql  = 'SELECT order_id, order_num, date, SUM(nett_price) as total, COUNT(DISTINCT date) as num_days, (ROUND(SUM(nett_price) / COUNT(DISTINCT date)), 2) AS daily_avg ';
	$sql .= 'FROM orders ';
	$sql .= 'WHERE complete = "Y" AND tx_type = "PAYMENT" AND date >= "'.$_POST['start_date'].'" AND date <= "'.$_POST['finish_date'].'" ';

[busby]

Ive never seen an SQL statement like that before. im a rookie with this kind of stuff but i tried inserting it into my code and i couldnt get it to work.  Would the method i mentioned above work? having the extra SQL statement below the main one?

 

 

[Muddy_Funster]

could you elaborate on "doesn't work"?

[busby]

well...i swapped my SQL statement for the one you gave me, then I commented out everything else below that statement and just had the following.

 

		
$db_orders_query_result = mysql_query($sql, $db_connection);
print($db_orders_query_result['daily_avg']);

 

i ran the page but nothing shows up...no errors or anything..just blank

[fenway]

mysql_query() returns a resultset object, not the individual rows.

[busby]

thanks for that...so whats the correct way?

[busby]

i think its a problem with the SQL statement but i dont know what...when i use mysql_num_rows to see how many rows are returned it throws up an error. which usually means theres something wrong with the SQL...anybody??

[Muddy_Funster]

try changing:

$db_orders_query_result = mysql_query($sql, $db_connection);
print($db_orders_query_result['daily_avg']);

to:

$db_orders_query = mysql_query($sql, $db_connection);
$db_orders_query_result = mysql_fetch_assoc($db_orders_query);
print_r ($db_orders_query_result);

[busby]

thanks funster...changed it to your code but now i get this error

 

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in

 

which also leads me to believe its something in the SQL statement??

[Muddy_Funster]

sounds likely, append

 or die ("ERROR:  >>  ".mysql_error());

to both the lines with mysql_query() and mysql_fetch_assoc() (taking out the ; that is already there) and we will get an ide of where it's all going wrong.

[fenway]

The sticky for this board covers simple debugging techniques like this.

[busby]

ok funster i attached it to the end of those 2 lines and i get this error now

 

ERROR: >> Operand should contain 1 column(s)

 

so im assuming it means the SELECT statement isnt returning any data?

[busby]

ok...ive spotted a flaw in all of this. even if i got this SQL to work it wouldnt give me the average sales figures for each day. because it could occurr that some days may have zero orders therefore wont appear in the database orders table. so if someone types in september 1st and september 10th...but september the 5th didnt have any orders it would only be counting 9 days and not 10...giving the wrong result.

 

so.......back to square one....see original post lol

[Muddy_Funster]

hmm, give this a shot then:

$start = $_POST['start_date'];
$end = $_POST['finish_date'];
$sql  = "SELECT order_id, order_num, date, SUM(nett_price) as total, (SUM(nett_price) / DATEDIFF('$end', '$start')) AS daily_avg FROM orders WHERE ((complete = 'Y') AND (tx_type ='PAYMENT') AND (date BETWEEN '$start' AND '$end'))";

also update youre or die() to read:

or die("ERROR: >> <BR>".mysql_error()."<BR> this occured trying to run the following query: <BR> $sql");