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

date: Wed, 14 Nov 2007 10:17:03 -0800   author:   Wayne

Re: Counting in SQL Queries   

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

date: Fri, 16 Nov 2007 00:56:17 +0100   author:   Hugo Kornelis LID