(My)SQL syntax question

[eluser]Bogdan Tanase[/eluser]
Hi, I'd be grateful if you could give me a hand with the following problem. I'm not very sure how to put it in English so I'll setup a simple example.

Let's assume I have this query (very much simplified for clarity):

Code:

SELECT *, SUM(price) as categ_total FROM products
GROUP BY categ_id


/* now, I'd like to put a condition on this query on the aggregated result */

SELECT *, SUM(price) as categ_total FROM products
WHERE categ_total>2000
GROUP BY categ_id

/* this would not work because `categ_total` is not known yet */

of course in the above example I could put SUM(price) in the WHERE clause and it should work, but if instead of the aggregate function I use a subquery things will get very ugly, having to repeat the subquery in the select clause and the where clause.

Views and temporary tables are my current solutions, but I was wondering if there are any 'smarter' ideas.

Thank you.

[eluser]xwero[/eluser]
i would split the sum sql statement from the row fetching as the grouping will have an unwanted result on the row fetching query.

[eluser]Bogdan Tanase[/eluser]
could you elaborate a little? the sum function was an example. It might have been a subquery without any kind of grouping...

I was interested if it's possible to condition the result table using "dynamic" results (results obtained with functions or subqueries).

[eluser]sl3dg3hamm3r[/eluser]
try this:

Code:

SELECT *, SUM(price) as categ_total FROM products
GROUP BY categ_id
HAVING SUM(price) > 2000

edit: ah sorry, didn't read your post 'till the end...

[eluser]Bogdan Tanase[/eluser]
Thanks.

I was looking at the Having clause now and it's exactly what I need.

In MySQL at least it works with subquery results also, not only with aggregate functions

Cool!