SQLTeam.com | Weblogs | Forums

How to write Sql server script

I have a table called Student, which has columns StudentName, SnackName, and DateOfSnack. I would like to query to list the Name of Students who snacked more than two items on a single day.
Student

This seems like assignment / homework.

What have you tried yourself? Have you covered the GROUP BY clause in class? (That's a hint.)

1 Like

Select studentname ,
count(snackname),
datepart (day, dateofsnack)
From student
Group by
studentname,
Datepart (day, dateofsnack)
Having count(snackname) > 2

hello Harishgg1,

When I run the script it doesn't show any answer. Please help.1

please give me 10 minutes

i will post the correct solution

hi

looking at the data

and what you want

looks like I wrote the CORRECT SQL

there are no results
because no data matches the SQL ( what you want)
would like to query to list the Name of Students who snacked more than two items on a single day.

1 Like

Hello Harishgg1,

Yes. I got the result from your script. I want to show the DateOfSnack on my result also.
How can I do?
I would like to query to list the Name of Students who snacked more than One items on a single day.

Please help.
Thank you

Harishgg1, your SQL is not actually correct.

.................thanks Scott for pointing it out ..........

I think it has to do with

Datepart(day, dateofsnack)
This is not correct

That has to be
Cast ( dateofsnack as date )

hi jay

i modified your data
i made
ian dateofsnack all 2019-04-01

also in the sql i changed
Datepart(day, dateofsnack)
to
Cast (dateofsnack AS DATE)

now the query is working ...

drop create data ...
drop table #student 
go 
 
create table #student  
(
studentname varchar(100),
snackname varchar(100),
dateofsnack datetime 
)
go

insert into #student select  'Saru','Chex Mix','2019-04-03 06:00:00.000'
insert into #student select  'Saru','Chips'   ,'2019-04-03 09:04:00.000'
insert into #student select  'Nima','Chex Mix','2019-04-02 08:00:00.000'
insert into #student select  'Nima','Chips'   ,'2019-04-03 06:00:00.000'
insert into #student select  'Ian' ,'Chex Mix','2019-04-01 06:10:00.000'
insert into #student select  'Ian' ,'Chips   ','2019-04-01 06:20:00.000'
insert into #student select  'Ian' ,'Chex Mix','2019-04-01 11:07:00.000'
go  

select * from #student
go
sql ..
SELECT studentname, 
       Count(snackname), 
       Cast (dateofsnack AS DATE) 
FROM   #student 
GROUP  BY studentname, 
          Cast (dateofsnack AS DATE) 
HAVING Count(snackname) > 2

image

1 Like

Hello Harishgg1,

Thank you.