[snip]
Is there any way in SQL (and especially MySQL) that I can determine
the count of rows that would be returned by a SELECT with a GROUP BY
and a HAVING, without retrieving the rows?
For example, I want the number of orders whose total value is greater
that 10000. The orders would be SELECTed like this:
SELECT orders.*, SUM(order_items.price) AS order_value
FROM orders, order_items
WHERE order_items.order_id = orders.id
GROUP BY orders.id
HAVING order_value > 10000
As I understand it, HAVING is the last filter to be applied in a
SELECT, so any COUNT in the column list will not be influenced by it.
So I think that what I want is impossible without sub-selects. Am I
right?
[/snip]
I haven't tested this, but try
SELECT orders.*, COUNT(SUM(order_items.price) AS order_value)
FROM orders, order_items
WHERE order_items.order_id = orders.id
GROUP BY orders.id
HAVING order_value > 10000
You can construct fairly complex functions in the SELECT clause and there
are lots of available functions that can be used. A little experimentation
with those functions will yield some pretty cool results. You may have to
muck around with the conditional clauses to get what you want.
HTH!
Jay
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
