CaptainChainsaw Posted January 30, 2013 Share Posted January 30, 2013 Hi all, The SQL I'm looking at calculates the the average age within the select and this works fine: (AVG((TO_DAYS(NOW())-TO_DAYS(pl.dob)))/365.242199) AS averageage The problem is when I want to count the ages that are within age groups. The example below should give the total number of ages within the 18-24 group. I can't get this to work, it displays zeros when the select statement is executed, there aren't any sql errors. SUM((case when (TO_DAYS(NOW())-TO_DAYS(pl.dob)/365.242199) BETWEEN 18 AND 24 then 1 else 0 end)) AS '18-24', Can anyone suggest a solution to this? CaptainChainsaw Quote Link to comment https://forums.phpfreaks.com/topic/273830-selecting-age-group-sql-select-problem/ Share on other sites More sharing options...
Barand Posted January 30, 2013 Share Posted January 30, 2013 '18-24' should be `18-24` Backticks, not quotes Quote Link to comment https://forums.phpfreaks.com/topic/273830-selecting-age-group-sql-select-problem/#findComment-1409157 Share on other sites More sharing options...
CaptainChainsaw Posted January 30, 2013 Author Share Posted January 30, 2013 Hi, Unfortunately backticks isn't the issue, works fine with either. Think the problem is deeper. Cheers, CaptainChainsaw Quote Link to comment https://forums.phpfreaks.com/topic/273830-selecting-age-group-sql-select-problem/#findComment-1409159 Share on other sites More sharing options...
Psycho Posted January 30, 2013 Share Posted January 30, 2013 (edited) I think you need to show the entire query. A SUM() needs a GROUP BY in order to actually sum anything. And, I believe, it is the GROUP BY that will need to have the logic to group by the age groups. It would also be helpful if you were to show the age groups you will have. Edited January 30, 2013 by Psycho Quote Link to comment https://forums.phpfreaks.com/topic/273830-selecting-age-group-sql-select-problem/#findComment-1409160 Share on other sites More sharing options...
Barand Posted January 30, 2013 Share Posted January 30, 2013 Both the TO_DAYS() need to be inside ()s. At present you have A - B/C instead of (A-B)/C Quote Link to comment https://forums.phpfreaks.com/topic/273830-selecting-age-group-sql-select-problem/#findComment-1409162 Share on other sites More sharing options...
Barand Posted January 30, 2013 Share Posted January 30, 2013 Hi, Unfortunately backticks isn't the issue, works fine with either. Think the problem is deeper. Cheers, CaptainChainsaw Don't you mean that it fails with either at the moment? Quote Link to comment https://forums.phpfreaks.com/topic/273830-selecting-age-group-sql-select-problem/#findComment-1409163 Share on other sites More sharing options...
CaptainChainsaw Posted January 30, 2013 Author Share Posted January 30, 2013 Thank you Barand, adding the brackets is now producing results. I'll need to check it further to see that the results are correct - looking good though! Cheers, CaptainChainsaw Quote Link to comment https://forums.phpfreaks.com/topic/273830-selecting-age-group-sql-select-problem/#findComment-1409177 Share on other sites More sharing options...
CaptainChainsaw Posted February 1, 2013 Author Share Posted February 1, 2013 Those results seem to be correct - thanks for your help Barand - all sorted Quote Link to comment https://forums.phpfreaks.com/topic/273830-selecting-age-group-sql-select-problem/#findComment-1409470 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.