NYCPHP Meetup

[Adam Maccabee Trachtenberg]

On Mon, 6 Oct 2003, Phil Powell wrote:

> I want to select the max(revenue) from bonus where revenue < 8411.
> Here is the query I tried:
> 
> select revenue, bonus
> from bonuses
> where revenue < 8411
> having max(revenue) < 8411
> 
> This produces no results, so, not understanding honestly (even after
> reading mysql.com ' s tutorials on WHERE vs HAVING), I used my
> "hammer-peg-into-hole" comprehension process and kept at it:

You use WHERE when you want to place restrictions upon your query
based on upon information already in the table. You use HAVING when
you need to restrict based upon the results of an aggregate function
(like count() and average()) and a GROUP BY clause.

While max() is an aggregate function, you don't really want to use
HAVING here; in fact, it doesn't make any sense at all.  That's
SQL-specific, not MySQL.

A good example of when to use HAVING is something like this: You have
a table containing order, item, and price. (You can have multiple
items in an order.)

To find all the items for a specific order:

SELECT * FROM sales WHERE order = $order;

To find the total sales for each order:

SELECT SUM(price) AS total FROM sales GROUP BY order;

To find only the orders with total sales of $50 or more:

SELECT SUM(price) AS total FROM sales GROUP BY order HAVING total >= 50;

You can't do a WHERE here, since total is not a column in any
particular row. And you don't want to only have items with price >=
50, since 2 $25 items in one order still give you a $50 total.

-adam

-- 
adam at trachtenberg.com
author of o'reilly's php cookbook
avoid the holiday rush, buy your copy today!