+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: (My)SQL syntax question (/showthread.php?tid=19253)
(My)SQL syntax question - El Forum - 06-02-2009
[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.
(My)SQL syntax question - El Forum - 06-02-2009
[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.
(My)SQL syntax question - El Forum - 06-02-2009
[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).
(My)SQL syntax question - El Forum - 06-02-2009
[eluser]sl3dg3hamm3r[/eluser]
try this:
Code:
SELECT *, SUM(price) as categ_total FROM products
GROUP BY categ_id
HAVING SUM(price) > 2000edit: ah sorry, didn't read your post 'till the end...
(My)SQL syntax question - El Forum - 06-02-2009
[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!