if value exists

[birwin]

I have a query that supplies a list of employees, ranked by total sales. I want to be able to create that list so it only shows employees who had sales in the last month for which the database has sales. I am using a joined table.

I can't do it as a separate query, although that would be much easier, since this query also ranks the employees. With a separate query it ranks based on all employees in the database, not just those with sales in the last month.

// get the oldest date in the table
$result_get_last_date=mysql_query("select max(sales_date) from table);

list($max_sales_date)=mysql_fetch_row($result_get_last_date);

// find the results
The ideal query, if it would work, would be:
mysql_query("select a.emp_no, i.f_name, i.l_name, sum(a.months_sales) as provsales from empsales as a, empinfo as i where a.emp_no=i.emp_no_info AND max(a.sales_date)='$max_sales_date' group by a.emp_no order by i.store_no, provsales desc", $conn)

I get an illegal use of group function error.

The key problem here is that mysql won't let me do a comparison to a max( ) statement. The max(a.sales_date)='$max_sales_date' causes the error.

Any ideas how to accomplish this?

Thank you.

Brian

[LordShryku]

When grouping, you'll have to group an any selected item that's not part of an aggregate function. So, for this, you'll have to group by a.emp_no, i.f_name, i.l_name.

You're also missing the "SELECT" part of the statement...

[birwin]

Thank you for your comment. I edited my original post to show the SELECT statement. That was just an oversight. My actual code has it.

My problem is that I need to be able to compare the employee's last month of employment with the last month showing in the file, which, by the way, is often not the last actual month. I need to do that in a way that preserves the integrity of the sum(a.months_sales) calculation.

In an ideal world it would do this as a single statement.

Does anyone know how to do this comparison and keep the sum() correct?

Thanks.

Brian

[LordShryku]

When doing numeric comparison against an aggregate function, you use the HAVING clause.
http://dev.mysql.com/doc/mysql/en/SELECT.html