← Back to Kurakani

Please help on Sql query

Original Post

​Hello , I have a table called Student consisting of Columns Name, and DateOfBirth. I would like to create a query Which Selects all Name whose DateOfBirth is on the same day. The DateOfBirth Column datatype is in DateTime format. I want the result in the Date format. In my table below I want row 1,2, and 7 for day 1 and 3,4 for day 2 as a result of the query.

phone
Jul 3, 2019 5:11 PM · 15,927 views
8 Replies
lazyketa Jul 3, 2019 6:36 PM
Use "GROUP BY" on DOB and get list of names in one column by using STRING AGG functions.
basnyatt Jul 3, 2019 6:38 PM
select DATE_OF_BIRTH, LISTAGG(NAME, ',') WITHIN GROUP (ORDER BY NAME) AS NAME from TABLE GROUP BY DATE_OF_BIRTH; Good Luck !!!
phone Jul 4, 2019 10:17 AM
Hello basnyatt, When I run the query it throws the error saying: "The function 'ListAgg' may not have a WITHIN GROUP clause."
basnyatt Jul 5, 2019 10:21 AM
what RDBMS are you using
raajkm Jul 5, 2019 12:18 PM
select x.*, rownum as day_num from ( select to_date((to_char(dateOfBirth, 'YYYY-MM-DD')),'YYYY-MM-DD') date_of_Birth, listagg(Name, ',') within group(order by Name) Name_of_students from student group by to_date((to_char(dateOfBirth, 'YYYY-MM-DD')),'YYYY-MM-DD') ) x; this works for Oracle. If you are using mysql try using string_agg instead of listagg.
phone Jul 6, 2019 1:21 PM
Hello basnyatt, I am using Microsoft SQL Server 2012.
phone Jul 7, 2019 11:37 AM
Hello raajkm, I need the skript for Microsoft SQL Server. Please help me. Last edited: 08-Jul-19 08:07 AM
raajkm Jul 8, 2019 12:14 PM
WELL i do not use sql server 2012 but you might use the query as; select to_date((to_char(S1.dateOfBirth, 'YYYY-MM-DD')),'YYYY-MM-DD') date_of_Birth, stuff ((select distinct ','+ Name from student s2 where s2.name=s1.name FOR XML PATH(' ')),1,1,' ') as name_of_students from student s1 group by to_date((to_char(S1.dateOfBirth, 'YYYY-MM-DD')),'YYYY-MM-DD') Last edited: 08-Jul-19 12:16 PM Last edited: 08-Jul-19 12:17 PM
Login to join the conversation.

You might be interested in...

Recent Classifieds View all
Upcoming Events View all
Service Providers View all
Ask Sajha AI Assistant