[Resolved] [Resolved] [Resolved] Simple SELECT COUNT,SUM query

[dougmcc1]

I'm having trouble with a simple query. All I want to do is find out how many times a name exists in a table and sum up the amount between 2 date ranges. Like this:

$query="SELECT name,COUNT(name) as count,SUM(amount) as sumamount FROM table WHERE (date BETWEEN '$date1' AND '$date2') AND orders > 0";
$result=mysql_query($query);
while($row=mysql_fetch_assoc($result)) {
$name=$row['name'];
$count=$row['count'];
$sumamount=$row['$sumamount'];

echo "$name exists $count times between $date1 and $date2. Total amount is $sumamount.<BR>";
}

What am I doing wrong?

[laserlight]

What's the problem?

[devinemke]

what is the error (if any)? use [man]mysql_error[/man] to see what's going wrong:

$result=mysql_query($query) or exit(mysql_error());

[dougmcc1]

It said to do a group by so I modified my query and now it says "Unknown column 'date' in 'having clause' ".

I have a date column and the table name in my query is correct. It probably doesnt matter but the date column is also set as a date data type, it is populated with data, and the data is in the correct format (2005-06-21).

Here's my modified query:

$query="SELECT name,COUNT(name) as count,SUM(amount) as sumamount FROM table GROUP BY name HAVING (date BETWEEN '$date1' AND '$date2') AND orders > 0";

Thanks for the help so far.

[dougmcc1]

Working query:

$query="SELECT Date,name,COUNT(name) as count,SUM(amount) as sumamount FROM table GROUP BY name HAVING (date BETWEEN '$date1' AND '$date2') AND orders > 0";