How do I use a Less than or greater than symble in the WHERE statement with a SUM feature

I want it to list the records if the total number of Yes's are less than 5.

I am getting an error:

An aggregate may not appear in the WHERE clause unless it is 开发者_如何学JAVAin sub query contained in a HAVING clause or select list , and the column being aggregated is an outer reference

I want to list students who are in WCM103, and have attended less than 5 classes.

---

Would something like:

WITH att
  AS (SELECT studentID, 
             SUM(CASE WHEN attStatus = 'Yes' THEN 1 ELSE 0 END) as att_count
        FROM attendance
       GROUP BY studentID)
SELECT *
  FROM Attendance a 
 INNER JOIN Student s USING (studentID)
 INNER JOIN att USING (studentID)
 WHERE att.att_count < 5
   AND a.unitcode = 'SIT103';

work for you?

You could select the columns from STUDENT and ATTENDANCE you need etc.

EDIT: I don't have a SQL interface in front of me tody so some of the SQL might need tweaking.

David, in light of the new info, try the following:

WITH att
  AS (SELECT unitcode,
             studentID, 
             SUM(CASE WHEN attStatus = 'Yes' THEN 1 ELSE 0 END) as att_count
        FROM attendance
       WHERE attdate < TO_DATE('07/08/2011', 'DD/MM/YYYY')
       GROUP BY unitcode,
                studentID)
SELECT *
  FROM Student s
 INNER JOIN att USING (studentID)
 WHERE att.unitcode = 'SIT103'
   AND att.att_count < 5;

I have SQL*Plus running again now. I just ran:

CREATE TABLE student (
  studentid NUMBER,
  student_name VARCHAR2(30)
);

CREATE TABLE attendance (
  studentid NUMBER,
  unitcode VARCHAR2(10),
  attdate  DATE,
  attstatus VARCHAR2(5)
);

INSERT INTO student VALUES (2106,'Jo Bloggs');
INSERT INTO student VALUES (2108,'Jo Schmoe');

INSERT INTO attendance VALUES (2106, 'SIT103', TO_DATE('05/06/2011', 'DD/MM/YYYY'), 'No');
INSERT INTO attendance VALUES (2106, 'SIT103', TO_DATE('07/07/2011', 'DD/MM/YYYY'), 'Yes');
INSERT INTO attendance VALUES (2106, 'SIT103', TO_DATE('10/05/2011', 'DD/MM/YYYY'), 'Yes');
INSERT INTO attendance VALUES (2108, 'SIT203', TO_DATE('05/05/2011', 'DD/MM/YYYY'), 'Yes');

WITH att
  AS (SELECT unitcode,
             studentID, 
             SUM(CASE WHEN attStatus = 'Yes' THEN 1 ELSE 0 END) as att_count
        FROM attendance
       WHERE attdate < TO_DATE('07/08/2011', 'DD/MM/YYYY')
       GROUP BY unitcode,
                studentID)
SELECT studentid,
       student_name,
       unitcode
  FROM Student s
 INNER JOIN att USING (studentID)
 WHERE att.unitcode = 'SIT103'
   AND att.att_count < 5;

and got:

STUDENTID STUDENT_NAME UNITCODE
2106         Jo Schmoe    SIT103  

Which is correct isn't it?

---

To use having, you have to have a group by clause, the group by is then executed and the having acts as a filter after the query has been aggregated.

Not sure you want to do a sum, without seeing the data in the tables but you should be able to do a count of the number of yes in the attendance table and then the having will list those that meet the criteria

Try something like this (oracle SQL syntax)

SELECT s.studentname, count(*)
  FROM student s, attendance a
 WHERE s.studentid=a.studentid
   AND a.class='SIT108'
   AND a.status='Y'
 GROUP BY s.studentname
HAVING COUNT(*)<5

or with ANSI syntax

SELECT s.studentname, count(*)
  FROM student s
  LEFT INNER JOIN attendance a
    ON s.studentid=a.studentid
 WHERE a.class='SIT108'
   AND a.status='Y'
 GROUP BY s.studentname
HAVING COUNT(*)<5

You can omit the count(*) on the top line as well if you just want a list of names

SELECT s.studentname
  FROM student s
  LEFT INNER JOIN attendance a
    ON s.studentid=a.studentid
 WHERE a.class='SIT108'
   AND a.status='Y'
 GROUP BY s.studentname
HAVING COUNT(*)<5

Add whatever additional where clauses or joins you need

---

Try replacing WHERE with HAVING as in the following:

SELECT        * 
FROM            Attendance AS a INNER JOIN
                         Student AS s ON a.studentID = s.studentID
HAVING        (SUM(CASE WHEN attStatus = 'Yes' THEN 1 ELSE 0 END) < 5)