Jump to content


Photo

selecting age group (sql select problem)

sql select mysql

  • Please log in to reply
7 replies to this topic

#1 CaptainChainsaw

CaptainChainsaw

    Advanced Member

  • Members
  • PipPipPip
  • 90 posts

Posted 30 January 2013 - 10:53 AM

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
Vicetti Clothing www.vicetti.co.uk

#2 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,135 posts
  • LocationCheshire, UK

Posted 30 January 2013 - 11:04 AM

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

Backticks, not quotes

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#3 CaptainChainsaw

CaptainChainsaw

    Advanced Member

  • Members
  • PipPipPip
  • 90 posts

Posted 30 January 2013 - 11:17 AM

Hi,

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


Cheers,

CaptainChainsaw
Vicetti Clothing www.vicetti.co.uk

#4 Psycho

Psycho

    Advanced Member

  • Gurus
  • 10,726 posts
  • LocationCanada

Posted 30 January 2013 - 11:19 AM

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.

The quality of the responses received is directly proportional to the quality of the question asked.

I do not always test the code I provide, so there may be some syntax errors. In 99% of all cases I found the solution to your problem here: http://www.php.net

#5 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,135 posts
  • LocationCheshire, UK

Posted 30 January 2013 - 11:29 AM

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

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#6 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,135 posts
  • LocationCheshire, UK

Posted 30 January 2013 - 11:33 AM

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?

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#7 CaptainChainsaw

CaptainChainsaw

    Advanced Member

  • Members
  • PipPipPip
  • 90 posts

Posted 30 January 2013 - 11:56 AM

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
Vicetti Clothing www.vicetti.co.uk

#8 CaptainChainsaw

CaptainChainsaw

    Advanced Member

  • Members
  • PipPipPip
  • 90 posts

Posted 01 February 2013 - 05:01 AM

Those results seem to be correct - thanks for your help Barand - all sorted :)
Vicetti Clothing www.vicetti.co.uk




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com