I need help with this SQL query |
[eluser]Unknown[/eluser]
This does what I want: SELECT distinct residency, count(*) as count FROM users WHERE (active = 1) GROUP BY residency; I am struggling to get this into my model. So far I have: Code: function get_residency() How do I put the count as count part in??
[eluser]Tim Brownlaw[/eluser]
I just tried something similar and got this... You can add in extra Select Statements and for your count - it's best to use a primary index if you have one, Try to avoid using * and be specific. Code: function get_residency() Also you can loose the "From" statement and put the table name in the get Code: function get_residency() Those are just some of the ways you can achieve what you want.
[eluser]Rolly1971[/eluser]
all you need to do is change what your function returns, eg: Code: function get_residency()
[eluser]Tim Brownlaw[/eluser]
Time to crack this open and see whats what! For testing purposes I'm using one of my existing tables called members that has a field called timezones. Or I could have said "the names have been changed to protect the innocent...." but that's a bit lame! Case 1 Code: echo '<h4>Case 1</h4>'; The Results of this are Code: SELECT DISTINCT `timezone`, count(id) as count FROM (`members`) GROUP BY `timezone` Code: array (size=3) So we get the Fieldname's values along with their respective counts Case 2 Code: echo '<h4>Case 2</h4>'; The results are Code: int 2 Code: SELECT COUNT(*) AS `numrows` FROM (`members`) GROUP BY `timezone` ORDER BY `timezone` desc Code: array (size=3) So Case 1 gives the required results but interestingly in Case 2 - count_all_results is creating the correct SQL BUT it only returns the result of the first row. If you change the desc to asc in the order_by in Case 2 - you'll get a result of 1... Also, the code in DB_active_rec.php shows this to be the case. It's expecting a single row result. So in this situation, count_all_results isn't the way to go! @rolly - you also forgot the tell it what table to use! So what you suggested kind of went BANG! it intrigued me enough to test it out as I've never used it before We're always learning new things in here So when in doubt, knock up some test code and see what is actually happening! Hope that helps! Cheers Tim |
Welcome Guest, Not a member yet? Register Sign In |