Jump to content

Archived

This topic is now archived and is closed to further replies.

CaptainChainsaw

selecting age group (sql select problem)

Recommended Posts

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.