[RESOLVED] Find the SUM of query results.

eheia

I am using the following query to find the total times each lead has been sold.

<?php
$query = "SELECT * FROM Leads WHERE Date = '$Today' AND Source = 'MFE.com' ORDER BY CustomerID ASC LIMIT 0, 1000";
$result = mysql_query($query);
$num=mysql_num_rows($result);
while ($row = mysql_fetch_assoc($result)) {

$CustomerID = $row['CustomerID'];
$query1 = "SELECT COUNT(CustomerID) AS qtysold FROM Purchased_Leads WHERE CustomerID = '$CustomerID'";
$result1 = mysql_query($query1);
$row1 = mysql_fetch_assoc($result1)

}
?>

this returns the number of times sold for each lead in the query. From this info, i also need to get the total times sold for all the leads in the query. i'm probably nt explaining this the best.

I've checked all over and cannot find a resolution for this. any help will be GREATLY appreciated.

thanks.

laserlight

You probably have to use two queries. One for the individual counts, and another for the total. Another way of course would be to sum the total as you loop through the result set for the individual counts:

$query = "SELECT PL.CustomerID AS cid, COUNT(PL.CustomerID) AS qtysold
	FROM Purchased_Leads PL, Leads L
	WHERE L.Date='$Today' AND L.Source='MFE.com'
		AND L.CustomerID=PL.CustomerID
	ORDER BY PL.CustomerID
	GROUP BY PL.CustomerID";
$result = mysql_query($query);
$total = 0;
while ($row = mysql_fetch_assoc($result)) {
	$total += $row['qtysold'];
	echo $row['cid'] . ': ' . $row['qtysold'] . "<br />\n";
}
echo 'Total: ' . $total;

eheia

Thanks for your reply. It still is not working for me. I dont quite understand the query you used, but i think it may be the reason its not working. The only relation between the Leads table and Purchased_Leads table is the CustomerID field. so, i need to get the proper CustomerIDs from the Leads table, then using that info, query the Purchased_Leads table so it will pull all transactions where the CustomerIDs are equal. then get the total number of transactions for each lead, as well as the total number of transactions for ALL Leads on that specific date and source which is given in the original query. maybe i've been working on this for so long i'm making it harder than it really is? i dont know. maybe the query your wrote is really doing that, but i can't really tell.

eheia

here is my solution:

<?
$Today = date("Ymd");
$query = "Select p.CustomerID, l.CustomerID FROM Purchased_Leads p, Leads l WHERE l.Date = '$Today' AND l.CustomerID = p.CustomerID AND l.source = 'MFE.com'";
$result = mysql_query($query);
$num = mysql_num_rows($result);
?>

Then i just echo $num and it works.
Thanks for your help!