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.

Hello Harishgg1,

Now my problem is:

I have a table called Student, which has columns StudentName, SnackName, and DateOfSnack.
I would like to query to list the names of Students who snacked two different predefined items() on a single day. I want to show all the columns on my result also.

I have the below query and how can I add all columns?

SELECT studentname,
Count (DISTINCT snackname) as NumberOfSnack,
Cast (dateofsnack AS DATE) as NewSnackDate
FROM student
Where SnackName in('Chex Mix', 'Chips')
GROUP BY studentname,
Cast (dateofsnack AS DATE)
HAVING Count(DISTINCT snackname) > 1

Hi jay

It's bedtime for me

I will provide the answer

Tomorrow for sure

Please don't mind

Thanks

Harish

hi jay

is this okay ????
hope it helps
i love feedback :slight_smile: :slight_smile:

i added another row to data ... for studentname Sara with Lays

sara added extra row
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  'Saru','Lays'   ,'2019-04-03 12:04:00.000'

since you said Students who snacked two different predefined items() on a single day.
two
i put
HAVING Count(snackname) =2

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  'Saru','Lays'   ,'2019-04-03 12:04:00.000'


insert into #student select  'Nima','Chex Mix','2019-04-03 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 ...
;WITH cte 
     AS (SELECT studentname, 
                Count(snackname)           AS cntsnack, 
                Cast (dateofsnack AS DATE) cntsnackdate 
         FROM   #student 
         WHERE  snackname IN ( 'Chex Mix', 'Chips' ) 
         GROUP  BY studentname, 
                   Cast (dateofsnack AS DATE) 
         HAVING Count(snackname) = 2) 
SELECT a.* 
FROM   #student a 
       JOIN cte b 
         ON a.studentname = b.studentname

image

Hello Harishgg1,

Thank you for your reply. Let me clear my problem.

My question:-
I want to select all from Table Student where the same person eats snack 'Chex Mix' and snack 'Chips' on the same day.

Please help,
Thank you.

Hi

Please tell me in a way that I can understand

With data as example

Doing it is very very easy

Understanding what you want
Is very tough
:slightly_smiling_face::slightly_smiling_face::+1::+1:

Hello Harishgg1,

I have thousands of data. I want the answer like I have Selected the yellow colour on my sample data.
image

Hi jay

Got you..I think I understand

It's my bed time

I will give the solution

In the morning

For sure

Thanks

Harish

hi jay

i tried to do this
please see if its what you want ???

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-03 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'


insert into #student select  'Meena','Nuts'   ,'2019-04-03 08:00:00.000'
insert into #student select  'Meena','Chips'  ,'2019-04-05 06:00:00.000'
go  

select * from #student
go
SQL ...
;WITH cte 
     AS (SELECT studentname, 
                Count(snackname)           AS cntsnack, 
                Cast (dateofsnack AS DATE) cntsnackdate 
         FROM   #student 
         WHERE  snackname IN ( 'Chex Mix', 'Chips' ) 
         GROUP  BY studentname, 
                   Cast (dateofsnack AS DATE) 
         HAVING Count(snackname) = 2) 
SELECT a.*, 
       CASE 
         WHEN b.studentname IS NOT NULL THEN 'Y' 
       END AS LinesYellow 
FROM   #student a 
       LEFT JOIN cte b 
              ON a.studentname = b.studentname 
                 AND Cast(a.dateofsnack AS DATE) = b.cntsnackdate

image

1 Like

you forgot to highlight in yellow Meena cause this person ate twice in the same day.