Please help on Sql query

Posts 9 · Viewed 6282 · Likes 1 · Go to Last Post

· Snapshot 0

Like · Likedby · 0

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.

lazyketa

· Snapshot 49

Like · Liked by · 0

Use "GROUP BY" on DOB and get list of names in one column by using STRING AGG functions.

basnyatt

· Snapshot 52

Like · Liked by · 1

select DATE_OF_BIRTH, LISTAGG(NAME, ',') WITHIN GROUP (ORDER BY NAME) AS NAME
from TABLE
GROUP BY DATE_OF_BIRTH;

Good Luck !!!

phone

· Snapshot 162

Like · Liked by · 0

Hello basnyatt,

When I run the query it throws the error saying:
"The function 'ListAgg' may not have a WITHIN GROUP clause."

basnyatt

· Snapshot 259

Like · Liked by · 0

what RDBMS are you using

raajkm

· Snapshot 312

Like · Liked by · 0

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

· Snapshot 410

Like · Liked by · 0

Hello basnyatt,

I am using Microsoft SQL Server 2012.

phone

· Snapshot 489

Like · Liked by · 0

Hello raajkm,

I need the skript for Microsoft SQL Server. Please help me.

Last edited: 08-Jul-19 08:07 AM

raajkm

· Snapshot 642

Like · Liked by · 0

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

Please log in to reply to this post

You can also log in using your Facebook