Counting in SQL Queries

I am working on creating a Report using SRS but first have to get the correct
information out of my database with a query.
What I am attempting to do is create Honor Roll for one of my schools, and
the requirements for the Honor Roll is a gpa >3.0 and less than 3.99, and
they are only allowed to have one C of any type to be on the list.
I've got the majority of the query down but cannot get it to count
correctly for the number of C's that a student has. I thought I had it fixed
and it seems like it weeds out some names but leaves others.
Here is what I have so far in trying to get it to work.
Thanks in Advance for any suggesstions


SELECT DISTINCT s.lastname+','+' '+s.firstname AS Student,
s.studentnumber,e.grade,t.name AS Term,
gs.score, c.name AS Course, se.teacherdisplay
FROM GradingScore gs
INNER JOIN student s ON s.personid = gs.personid
INNER JOIN v_TermGpa tg ON tg.personid = s.personid
INNER JOIN enrollment e ON e.enrollmentid = s.enrollmentid AND e.calendarid
= gs.calendarid
INNER JOIN scheduleStructure ss ON ss.calendarid = gs.calendarid
INNER JOIN Termschedule ts ON ts.structureid = ss.structureid
JOIN Term t ON t.termscheduleid = ts.termscheduleid AND t.termid = gs.termid
INNER JOIN section se ON se.sectionid = gs.sectionid
INNER JOIN course c ON c.courseid = se.courseid
WHERE gs.calendarID = 20 AND t.name ='2nd 6wk' AND tg.term2gpa BETWEEN 3.0
AND 3.99 AND s.enddate IS NULL
AND gs.score <='C-'
AND EXISTS (select gs1.personid, gs1.score
from gradingscore gs1
inner join enrollment e1 on e1.personid = gs1.personid
inner join student s1 on s1.personid = gs1.personid
where gs1.score IN('C+','C', 'C-')
GROUP BY gs1.score, gs1.personid,lastname+','+' '+s.firstname,
s.studentnumber, e.grade,t.name,
gs.score, c.name, se.teacherdisplay
HAVING (count(gs1.score) <=1)
)
ORDER BY s.[student]

--
Wayne Hess

On Wed, 14 Nov 2007 10:17:03 -0800, Wayne wrote:

>I am working on creating a Report using SRS but first have to get the correct
>information out of my database with a query.
> What I am attempting to do is create Honor Roll for one of my schools, and
>the requirements for the Honor Roll is a gpa >3.0 and less than 3.99, and
>they are only allowed to have one C of any type to be on the list.
> I've got the majority of the query down but cannot get it to count
>correctly for the number of C's that a student has. I thought I had it fixed
>and it seems like it weeds out some names but leaves others.
>Here is what I have so far in trying to get it to work.
>Thanks in Advance for any suggesstions

Hi Wayne,

Your query is a bit too long and complicated for me to understand
everything you do, so I'll give some generic guidelines for you to work
into your solution. My example code search for orders with exactly one
line for a product that starts with 'D'

There are two ways to test for "exactly 1 of XXXX". The easiest to
understand is with a subquery with COUNT:

SELECT o.OrderNo
FROM Orders AS o
WHERE (SELECT COUNT(*)
FROM OrderLines AS ol
WHERE ol.OrderNo = o.OrderNo
AND o1.ProductName LIKE 'D%') = 1 ;


The alternative, that is especially useful if you have to display some
information from that single matching row (the productname in my
example) is to join the tables and add a NOT EXISTS subquery to search
for another matching row - the join eliminates orders without 'D'
products and the NOT EXISTS eliminates orders with more than one 'D'
product. The example below assumes that the combination of OrderNo and
OrderLineNo is the primary key for thhe OrderLines table.

SELECT o.OrderNo, ol.ProductName
FROM Orders AS o
INNER JOIN OrderLines AS ol
ON ol.OrderNo = o.OrderNo
AND ol.ProductName LIKE 'D%'
WHERE NOT EXISTS
(SELECT *
FROM OrderLines AS ol2
WHERE ol2.OrderNo = ol.OrderNo
AND ol2.ProductName LIKE 'D%'
AND ol2.OrderLineNo <> 0l.OrderLineNo) ;

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis