Help with SQL Query

Hey all,
My abridged table structure is:
UNQ Number Student ID Date Attendance
1 1 1-feb-05 Y
2 2 2-feb-05 N
3 3 3-feb-05 Y
4 4 4-feb-05 C
5 1 .
6 2 .
7 3
8 4
9 1
10 2
11 3
12 4
13 1
14 2
15 3
16 4
and so on:
I need an output like (between two dates):
Student ID Present Absent Cancelled
1 10 4 3
These numbers are just examples.
I know I can write a query like:
select a.[student id],count(distinct a.[unique number]) from attendancetable
as a where a.[attendance]='Y' and a.[date]>='01-feb-05' and
a.[date]<='01-feb-05' group by a.[student id]
to get a result like:
Student ID Present
1 10
But, how can I get all the three information in one output?
Thank you.Try the following.
SELECT StudentID,
count(CASE Attendance WHEN 'Y' THEN 1 ELSE 0 END) AS 'Present',
count(CASE Attendance WHEN 'N' THEN 1 ELSE 0 END) AS 'Absent',
count(CASE Attendance WHEN C' THEN 1 ELSE 0 END) AS 'Cancelled'
FROM Attendance_Table
WHERE Date>= YourStartDate and Date <= YourEnddate
GROUP BY StudentID
GO
Gavin|||Thanks plenty!
A little modification:
SELECT distinct [Student ID],
count(CASE WHEN attendance='Y' THEN 1 ELSE null END) AS 'Present',
count(CASE WHEN attendance='N' THEN 1 ELSE null END) AS 'Absent',
count(CASE when Attendance='C' THEN 1 ELSE null END) AS 'Cancelled'
FROM StudentScheduleAttendanceDetails
WHERE Date>= '01-jan-03' and Date <= '01-feb-06' GROUP BY [Student ID] order
by [student id]
GO
did it. Thanks again!
"celtic_kiwi" <gavin.jolly@.gmail.com> wrote in message
news:1107658677.037863.312350@.f14g2000cwb.googlegroups.com...
> Try the following.
> SELECT StudentID,
> count(CASE Attendance WHEN 'Y' THEN 1 ELSE 0 END) AS 'Present',
> count(CASE Attendance WHEN 'N' THEN 1 ELSE 0 END) AS 'Absent',
> count(CASE Attendance WHEN C' THEN 1 ELSE 0 END) AS 'Cancelled'
> FROM Attendance_Table
> WHERE Date>= YourStartDate and Date <= YourEnddate
> GROUP BY StudentID
> GO
> --
> Gavin
>|||One other thing:
Is it possible to show Present / Total as a column ?
SELECT distinct a.[Student ID],b.[First Name],B.[Last Name],b.[E-Mail],
count(CASE WHEN a.[attendance]='Y' THEN 1 ELSE null END) AS 'Present',
count(CASE WHEN a.[attendance]='N' THEN 1 ELSE null END) AS 'Absent',
count(CASE when a.[Attendance]='C' THEN 1 ELSE null END) AS 'Cancelled',
count(CASE when a.[Attendance] = '' then 1 else null END) as 'Future',
count(CASE when a.[Attendance] like '%' then 1 else null END) as 'Total'
FROM StudentScheduleAttendanceDetails as a,StudentPersonalDetails as B
WHERE Date>= '01-jan-01' and
Date <= '01-jan-08'
and a.[student id]<>0 and b.[student id]=a.[student id]
and a.[student id] like '%'
GROUP BY a.[Student ID],b.[first name],b.[last name],b.[e-mail] order by
a.[student id]
somthing like:
SELECT distinct a.[Student ID],b.[First Name],B.[Last Name],b.[E-Mail],
count(CASE WHEN a.[attendance]='Y' THEN 1 ELSE null END) AS 'Present',
count(CASE WHEN a.[attendance]='N' THEN 1 ELSE null END) AS 'Absent',
count(CASE when a.[Attendance]='C' THEN 1 ELSE null END) AS 'Cancelled',
count(CASE when a.[Attendance] = '' then 1 else null END) as 'Future',
count(CASE when a.[Attendance] like '%' then 1 else null END) as 'Total'
(Present / Total) * 100 as "Present Ratio"
FROM StudentScheduleAttendanceDetails as a,StudentPersonalDetails as B
WHERE Date>= '01-jan-01' and
Date <= '01-jan-08'
and a.[student id]<>0 and b.[student id]=a.[student id]
and a.[student id] like '%'
GROUP BY a.[Student ID],b.[first name],b.[last name],b.[e-mail] order by
a.[student id]
So, I get something like
1011 A B a@.b.com 10 20 30 5 65 15.3
Can you please tell me if this can be done?
Thank you
Vince
"Vince" <nmvkPLEASERMVTHIS@.vsnl.net> wrote in message
news:uC63Lr$CFHA.2620@.tk2msftngp13.phx.gbl...
> Thanks plenty!
> A little modification:
> SELECT distinct [Student ID],
> count(CASE WHEN attendance='Y' THEN 1 ELSE null END) AS 'Present',
> count(CASE WHEN attendance='N' THEN 1 ELSE null END) AS 'Absent',
> count(CASE when Attendance='C' THEN 1 ELSE null END) AS 'Cancelled'
> FROM StudentScheduleAttendanceDetails
> WHERE Date>= '01-jan-03' and Date <= '01-feb-06' GROUP BY [Student ID]
order
> by [student id]
> GO
> did it. Thanks again!
> "celtic_kiwi" <gavin.jolly@.gmail.com> wrote in message
> news:1107658677.037863.312350@.f14g2000cwb.googlegroups.com...
>|||I think I have to only use a stored procedure, declare the variables, and
select the variable...
"Vince" <nmvkPLEASERMVTHIS@.vsnl.net> wrote in message
news:umGSdBADFHA.2600@.TK2MSFTNGP09.phx.gbl...
> One other thing:
> Is it possible to show Present / Total as a column ?
>
> SELECT distinct a.[Student ID],b.[First Name],B.[Last Name],b.[E-Mail],
> count(CASE WHEN a.[attendance]='Y' THEN 1 ELSE null END) AS 'Present',
> count(CASE WHEN a.[attendance]='N' THEN 1 ELSE null END) AS 'Absent',
> count(CASE when a.[Attendance]='C' THEN 1 ELSE null END) AS 'Cancelled',
> count(CASE when a.[Attendance] = '' then 1 else null END) as 'Future',
> count(CASE when a.[Attendance] like '%' then 1 else null END) as 'Total'
> FROM StudentScheduleAttendanceDetails as a,StudentPersonalDetails as B
> WHERE Date>= '01-jan-01' and
> Date <= '01-jan-08'
> and a.[student id]<>0 and b.[student id]=a.[student id]
> and a.[student id] like '%'
> GROUP BY a.[Student ID],b.[first name],b.[last name],b.[e-mail] order by
> a.[student id]
> somthing like:
> SELECT distinct a.[Student ID],b.[First Name],B.[Last Name],b.[E-Mail],
> count(CASE WHEN a.[attendance]='Y' THEN 1 ELSE null END) AS 'Present',
> count(CASE WHEN a.[attendance]='N' THEN 1 ELSE null END) AS 'Absent',
> count(CASE when a.[Attendance]='C' THEN 1 ELSE null END) AS 'Cancelled',
> count(CASE when a.[Attendance] = '' then 1 else null END) as 'Future',
> count(CASE when a.[Attendance] like '%' then 1 else null END) as 'Total'
> (Present / Total) * 100 as "Present Ratio"
>
> FROM StudentScheduleAttendanceDetails as a,StudentPersonalDetails as B
> WHERE Date>= '01-jan-01' and
> Date <= '01-jan-08'
> and a.[student id]<>0 and b.[student id]=a.[student id]
> and a.[student id] like '%'
> GROUP BY a.[Student ID],b.[first name],b.[last name],b.[e-mail] order by
> a.[student id]
> So, I get something like
> 1011 A B a@.b.com 10 20 30 5 65 15.3
> Can you please tell me if this can be done?
> Thank you
> Vince
>
> "Vince" <nmvkPLEASERMVTHIS@.vsnl.net> wrote in message
> news:uC63Lr$CFHA.2620@.tk2msftngp13.phx.gbl...
> order
>|||Glad I could help. I am new to this group and have a few questions open
so feel I should at least answer what I can.
Sorry, I should have left the function as SUM. The following will work
as well.
SELECT distinct [Student ID],
sum(CASE WHEN attendance='Y' THEN 1 ELSE 0 END) AS 'Present',
sum(CASE WHEN attendance='N' THEN 1 ELSE 0 END) AS 'Absent',
sum(CASE when Attendance='C' THEN 1 ELSE 0 END) AS 'Cancelled'
FROM StudentScheduleAttendanceDetails
WHERE Date>= '01-jan-03' and Date <= '01-feb-06' GROUP BY [Student ID]
order
by [student id]
GO|||Just guessing but this should work. But I have not tried doing
calculations on groups.
SELECT distinct a.[Student ID],b.[First Name],B.[Last Name],b.[E-Mail],
count(CASE WHEN a.[attendance]='Y' THEN 1 ELSE null END) AS 'Present',
count(CASE WHEN a.[attendance]='N' THEN 1 ELSE null END) AS 'Absent',
count(CASE WHEN a.[Attendance]='C' THEN 1 ELSE null END) AS
'Cancelled',
count(CASE WHEN a.[Attendance] = '' THEN 1 ELSE null END) as 'Future',
count(a.[Attendance]) as 'Total'),
count(CASE a.[attendance]
WHEN 'Y' THEN 1
WHEN 'N' THEN 1
WHEN 'C' THEN 1
ELSE null END) / count(a.[Attendance]) as 'Total')*100 as 'Present
Ratio'
FROM StudentScheduleAttendanceDetails as a,StudentPersonalDetails as B
WHERE Date>= '01-jan-01' and
Date <= '01-jan-08' and
a.[student id]<>0 and
b.[student id]=a.[student id]
/* and a.[student id] like '%' NOT SURE WHAT THIS LINE IS FOR' */
GROUP BY a.[Student ID],b.[first name],b.[last name],b.[e-mail]
ORDER BY a.[student id]|||Thanks Gavin! Really appreciate your help.
"celtic_kiwi" <gavin.jolly@.gmail.com> wrote in message
news:1107664607.420207.136340@.g14g2000cwa.googlegroups.com...
> Glad I could help. I am new to this group and have a few questions open
> so feel I should at least answer what I can.
> Sorry, I should have left the function as SUM. The following will work
> as well.
> SELECT distinct [Student ID],
> sum(CASE WHEN attendance='Y' THEN 1 ELSE 0 END) AS 'Present',
> sum(CASE WHEN attendance='N' THEN 1 ELSE 0 END) AS 'Absent',
> sum(CASE when Attendance='C' THEN 1 ELSE 0 END) AS 'Cancelled'
> FROM StudentScheduleAttendanceDetails
> WHERE Date>= '01-jan-03' and Date <= '01-feb-06' GROUP BY [Student ID]
> order
> by [student id]
> GO
>|||Again, thank you! It worked, I just had to cast as float...That [student id]
like '%' was to be like '3%' (all student ids beginning with 3)..
SELECT distinct a.[Student ID],b.[First Name],B.[Last Name],b.[E-Mail],
count(CASE WHEN a.[attendance]='Y' THEN 1 ELSE null END) AS 'Present',
count(CASE WHEN a.[attendance]='N' THEN 1 ELSE null END) AS 'Absent',
count(CASE WHEN a.[Attendance]='C' THEN 1 ELSE null END) AS 'Cancelled',
count(CASE WHEN a.[Attendance] = '' THEN 1 ELSE null END) as 'Future',
count(a.[Attendance]) as 'Total',
cast(cast(count(CASE when a.[attendance]='Y' THEN 1 else null end)as float)
/
count( a.[attendance])* 100 as decimal(4,0))
as 'Present Ratio (%)'
FROM StudentScheduleAttendanceDetails as a,StudentPersonalDetails as B
WHERE Date>= '01-jan-01' and
Date <= '01-jan-08' and
a.[student id]<>0 and
b.[student id]=a.[student id]
GROUP BY a.[Student ID],b.[first name],b.[last name],b.[e-mail]
ORDER BY a.[student id]
Again, thanks for your help.
"celtic_kiwi" <gavin.jolly@.gmail.com> wrote in message
news:1107666089.497943.221340@.g14g2000cwa.googlegroups.com...
> Just guessing but this should work. But I have not tried doing
> calculations on groups.
> SELECT distinct a.[Student ID],b.[First Name],B.[Last Name],b.[E-Mail],
> count(CASE WHEN a.[attendance]='Y' THEN 1 ELSE null END) AS 'Present',
> count(CASE WHEN a.[attendance]='N' THEN 1 ELSE null END) AS 'Absent',
> count(CASE WHEN a.[Attendance]='C' THEN 1 ELSE null END) AS
> 'Cancelled',
> count(CASE WHEN a.[Attendance] = '' THEN 1 ELSE null END) as 'Future',
> count(a.[Attendance]) as 'Total'),
> count(CASE a.[attendance]
> WHEN 'Y' THEN 1
> WHEN 'N' THEN 1
> WHEN 'C' THEN 1
> ELSE null END) / count(a.[Attendance]) as 'Total')*100 as 'Present
> Ratio'
> FROM StudentScheduleAttendanceDetails as a,StudentPersonalDetails as B
> WHERE Date>= '01-jan-01' and
> Date <= '01-jan-08' and
> a.[student id]<>0 and
> b.[student id]=a.[student id]
> /* and a.[student id] like '%' NOT SURE WHAT THIS LINE IS FOR' */
> GROUP BY a.[Student ID],b.[first name],b.[last name],b.[e-mail]
> ORDER BY a.[student id]
>