question about select query

there is a report which displays the enquiries made by users in the below mentioned format.
the count column refers to how many number of times an enquiry was made for a particular city

count city price

2 sydney $100
1 perth $200
2 melbourne $300

the total value of the enquiries should be $1000 ( 2 X $100 + 1 X $200 + 2 X $300) presently i am able to add the price column which displays $600 actually it should be $1000

following is the code presently

==============================================================================

mysql_select_db($database_connEnquiry, $connEnquiry);

$sql = " SELECT Count(*) as Counts, Title, City, Price, FROM `enquiry` WHERE date_format(en_date,'%Y-%m-%d') BETWEEN '" . $startDate . "' AND '" . $endDate . "' " . "
Group By Title, City, Price Order By Counts Desc, Title Desc, mode, Price";

$rs_newEnquiries = mysql_query($sql, $connEnquiry) or die(dbError(query1));

$echoStr = "<table width='80%' cellpadding=1 cellspacing=1 border=0><tr bgcolor='#cccccc;'><th bgcolor='#cccccc'
width=10>Count</th><th bgcolor='#cccccc' width=400>Destination</th><th bgcolor='#cccccc' width=100>Direct
Enquiries</th><th bgcolor='#cccccc' width=100>Mode</th> <th bgcolor='#cccccc' width=50>Priced From</th>
<th bgcolor='#cccccc' width=35>numAdults</th> <th bgcolor='#cccccc' width=35>numChildren</th> <th bgcolor='#cccccc' width=30>numInfants</th> <th align='center'

bgcolor='#cccccc'>Story</th> </tr>";

$total = 0; $totalprice = 0;

while ($row_rs_newEnquiries = mysql_fetch_assoc($rs_newEnquiries))
{
$echoStr .= "
<tr bgcolor='#ebebeb' class='default'><td>".$row_rs_newEnquiries['Counts']."</td>
<td class='default'>&nbsp; ".$row_rs_newEnquiries['Title']."</td>
<td class='default'>&nbsp; ".$row_rs_newEnquiries['City']."</td>
<td class='default'>&nbsp; ".$row_rs_newEnquiries['Price']."</td>
</tr>";
$total = $total + $row_rs_newEnquiries['Counts'];

$totalprice = $totalprice + $row_rs_newEnquiries['Price'];
}

$echoStr .= "<tr bgcolor='#cccccc;'><th align=\"left\" bgcolor='#cccccc'><b>".$total."</b></th>
<th bgcolor='#cccccc'>Total</th>

<td bgcolor='#cccccc'>&nbsp;</td><td bgcolor='#cccccc'></td><td bgcolor='#cccccc'><b>$&nbsp;".$totalprice."</b> </td><td bgcolor='#cccccc'><b>".$totalnumadults."</b></td><td

bgcolor='#cccccc'><b>".$totalnumchildren."</b></td><td bgcolor='#cccccc'><b>".$totalnuminfants."</b></td><td bgcolor='#cccccc'></td></tr></table>";

==============================================================================

With the above code i am able to add the value of the price and display the $ amount. however if the count for a city is for example 2 which means there were 2 enquiries for

example city sydney and the price is $100 then the total should be 2 X $100 which is $200 how can i change the above above code in a way that the $totalprice will be able to
1) multiply the numeric value in count column with the price column and similarly for all the rows
2) and add the $ amount in the price column and sum it up and be able to display in echo $totalprice;

please advice.

thanks.

 

hmm, i think you're getting confused with the count() function in SQL. COUNT(*) on the table you've provided should return 3 (there's a total of 3 records), or a total of 1 per row. You should avoid naming a db field with the same name as the name of an SQL function (ie - count). So let's say you name that field count_enquiry instead.

Change your SQL query to:

$sql = " SELECT count_enquiry, Title, City, Price, FROM `enquiry` WHERE date_format(en_date,'%Y-%m-%d') BETWEEN '" . $startDate . "' AND '" . $endDate . "' " . "
Group By Title, City, Price Order By count_enquiry Desc, Title Desc, mode, Price";

=====

Then change the line: $total = $total + $row_rs_newEnquiries['Counts']; to:

$total = $total + $row_rs_newEnquiries['count_enquiry'];

Then change the line: $totalprice = $totalprice + $row_rs_newEnquiries['Price']; to:

$totalprice = $totalprice + ($row_rs_newEnquiries['count_enquiry'] * $row_rs_newEnquiries['Price']);


that should give you what you need!