7 replies to this topic

[CaptainChainsaw]

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

[Barand]

'18-24' should be `18-24`

Backticks, not quotes

[CaptainChainsaw]

Hi,

Unfortunately backticks isn't the issue, works fine with either. Think the problem is deeper.


Cheers,

CaptainChainsaw

[Psycho]

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 by Psycho, 30 January 2013 - 11:20 AM.

[Barand]

Both the TO_DAYS() need to be inside ()s. At present you have A - B/C instead of (A-B)/C

[Barand]

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?

[CaptainChainsaw]

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

[CaptainChainsaw]

Those results seem to be correct - thanks for your help Barand - all sorted