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.
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.
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
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 
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
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
Hello Harishgg1,
I have thousands of data. I want the answer like I have Selected the yellow colour on my sample data.
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
1 Like
you forgot to highlight in yellow Meena cause this person ate twice in the same day.