SQL - How to display the students with the same age?

the cod开发者_运维技巧e I wrote only tells me how many students have the same age. I want their names too...

SELECT YEAR(CURRENT DATE-DATEOFBIRTH) AS AGE, COUNT(*) AS HOWMANY
FROM STUDENTS
GROUP BY YEAR(CURRENT DATE-DATEOFBIRTH);

this returns something like this:

AGE      HOWMANY
---      -------
21       3
30       5

Thank you.

TABLE STUDENTS COLUMNS:
StudentID (primary key), Name(varchar), Firstname(varchar), Dateofbirth(varchar)

I was thinking of maybe using the code above and somewhere add the function concat that will put the stundents' names on the same row as in

your existing SQL looks like it has errors, but you could use GROUP_CONCAT:

add GROUP_CONTACT(colname) as another column to fetch, then split by , in your application

The resulting data set does not appear useful on the surface based on the question unless you are looking for a listing of students, their age, and how many other students are of the same age:

SELECT NAME, AGE, HOWMANY
FROM STUDENTS AS S,
 (SELECT YEAR(CURRENT DATE-DATEOFBIRTH) AS AGE,
         COUNT(*) AS HOWMANY
  FROM STUDENTS
  GROUP BY YEAR(CURRENT DATE-DATEOFBIRTH)
 ) AS A
WHERE YEAR(CURRENT DATE-S.DATEOFBIRTH) = A.AGE

Basically perform a self-join with the age counts you have calculated.

What about...

SELECT name FROM students WHERE age = ENTER_AGE_HERE;

You have the names and the number of students can be found by finding the number of entries you get from the query.

For example, in PHP, you can find the length of the array.

Of course, you have to change to names in my example to the names used in your database.

CREATE TABLE #Student
(
 id int identity(1,1),
 age int,
 name varchar(255) 
)


INSERT INTO #Student S
VALUES(21,'bob'),
(21,'tom'),
(21,'dick'),
(21,'william'),
(35,'mark'),
(35,'anthony')

SELECT age,COUNT(*),STUFF(
(
  SELECT ',' + name
  FROM #Student SS
  WHERE SS.age = S.age
  FOR XML PATH('')
), 1, 1, '')
FROM #Student s
GROUP BY age