SQL Select Count from below a certain age

Question

I'm trying to get a count of people in a table below a certain age. Something like this

select count(*) as total from 'TABLE' where 'BIRTHDAY'> 19 years old ;

The field type is DATE for BIRTHDAY

I'm new to sql and I am just not sure how to write this properly.

Which DBMS are you using? Postgres? Oracle? – Jan 15 '14 at 15:45 — , Jan 15 '14 at 15:45
Love the Query being in simple English! – Maheswaran Ravisankar Jan 15 '14 at 15:51 — Maheswaran Ravisankar, Jan 15 '14 at 15:51

score 1 · Answer 1 · answered Jan 15 '14 at 15:49

1

In MySQL you can do:

select count(*) as total from TABLE where BIRTHDAY < CURDATE() - INTERVAL 19 YEAR;

And note: You don't want to use ' around tablename or column name, as this makes them a string and your statement fails.

answered Jan 15 '14 at 15:49

fancyPants

+1 for pointing out not to use the ' around table/column name to the OP – Mike Jan 15 '14 at 15:51
I was just using ' for emphasis. I got this error Dynamic SQL Error. SQL error code = -104. Token unknown - line 1, column 78. 19. – user3198861 Jan 15 '14 at 16:34
See? That's what you get for not specifying exactly which database system you're using ;) – fancyPants Jan 16 '14 at 07:43

score 1 · Accepted Answer · answered Jan 15 '14 at 15:50

1

For SQL Server and MySQL, you could look to use the DATEDIFF function

SELECT COUNT(*) FROM Table WHERE DATEDIFF(year,BIRTHDAY,today) > 19

(replacing the today bit with the relevant command for the RDBMS you are using to get today's date (ie: GETDATE() for SQL Server)

answered Jan 15 '14 at 15:50

Mike

2

Just FYI, don't know about SQL Server, but in MySQL for example an index couldn't be used when written like this, as the column is in a function. IIRC an index can be created on a function call, though. Still it's easier to just rearrange, so that the column is on one side and the calculation on the other. – fancyPants Jan 15 '14 at 15:55
But I don't know of any flavor of SQL where you can index on a function that includes a non-deterministic value like "today". But the problem is easily solved, use where birthday – Jay Jan 15 '14 at 16:14
I'm confused now - this code SELECT COUNT(*) FROM Employees WHERE DATEDIFF(year,DOB,GETDATE()) > 200 runs on SQL Server 2008 and returns the results I expect – Mike Jan 15 '14 at 16:17
1

Noone said the result wouldn't be right. Just that an index can not be used this way. – fancyPants Jan 15 '14 at 16:22
This worked! Thanks for all the advice. For firebird, I had to use cast('Now' as date) to replace the parameter today in the DATEDIFF function. – user3198861 Jan 15 '14 at 16:44

2 Answers2