David David - 1 month ago 5
SQL Question

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 in 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.

Answer

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?

Comments