0

I'm trying to print selected data from one of my tables in my database. I can successfully do this but I then have to print this grouped by age and in descending order. But I have no idea how to do this with only the date of birth. Any help is appreciated and my SQL is displayed below.

SELECT pet_id, Name, Type, Breed, Born
FROM pet
WHERE Born
BETWEEN '1999' AND '2001'
GROUP BY

Please feel free to ask questions. I haven't tried anything as I don't know where to start unfortunately.

Isaac Bennetch
  • 11,830
  • 2
  • 32
  • 43
DAN212
  • 51
  • 1
  • 2
  • 9

3 Answers3

3

I think you want to ORDER BY age rather than GROUP BY. You have no aggregation functions that suggest grouping.

So:

SELECT pet_id, Name, Type, Breed, Born
FROM pet
WHERE Born BETWEEN '1999' AND '2001'
ORDER BY Born ASC;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Sorry I lost my internet connection and only just got back online. The answer you provided doesn't order by age descending. It orders by date of birth descending. So in reality age descending would be oldest first whereas your answer sorts by youngest descending to oldest. I do see your point about ORDER BY instead of GROUP BY but I'm still looking for the answer unfortunately. – DAN212 Jun 13 '16 at 23:11
0

If the actual age in years is important output in the query, this question and answer may help calculate age using date of birth and current date: How to calculate age (in years) based on Date of Birth and getDate()

based on your initial query and what you are requesting for data, I don't see a need for a group by just an order by

SELECT pet_id, Name, [Type], Breed, Born, 
DATEDIFF(hour, Born, GETDATE())/8766 as 'Age'
FROM pet
WHERE Born
BETWEEN '1999' AND '2001'
ORDER BY DATEDIFF(hour, Born, GETDATE())/8766 DESC
Community
  • 1
  • 1
mfreedm52
  • 161
  • 10
  • Hi, I've tried using this but I keep getting an error. I have also switched to ms access so not sue if it's related. Here is the error that I receive: "The SELECT statement includes a reserved word or argument name that is misspelled or missing, or the punctuation is incorrect". Again thanks for your contunued assistance. – DAN212 Jun 14 '16 at 14:58
  • I've edited the query a bit. My guess is that the error is centered around your column name "Type", enclosing this in brackets should fix the problem. – mfreedm52 Jun 14 '16 at 17:01
  • Thanks that seems to have fixed that part but now there is a new error. Undefined function 'GETDATE' in expression. – DAN212 Jun 14 '16 at 17:04
  • If you are using mySQL this function is called Now() – mfreedm52 Jun 14 '16 at 18:25
  • I'm doing this inside ms access so I'll give it a go and let you know what happens. – DAN212 Jun 14 '16 at 19:05
  • Once I run the SQL command with NOW() I am prompted to input the hour but I've typed in several time formats and random numbers but I keep getting the same error each time. The error is: The expression is typed incorrectly, or it is too complex to be evaluated. – DAN212 Jun 15 '16 at 11:41
0

If you are trying to run this SQL script in MS Access try something like this:

SELECT pet_id, Name, Type, Breed, Born
FROM pet
WHERE Born BETWEEN '1999' AND '2001'
ORDER BY DateDiff("yyyy",DateSerial([Born],1,1),DateSerial(Year(Date()),1,1)) DESC;

I am assuming that the 'Born' field is not actually a date field type but rather a Number type or Text type.

IT2428
  • 21
  • 1
  • 9