Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003
    Posts
    113

    Unanswered: why column function can't be in where-clause

    How to get all the PERSON.NAME that has total workload <= 100? Thx
    Why can't let column function in where-clause? Thx

    DDL:
    CREATE TABLE PERSON
    (NAME CHAR(8), DEPT_ID CHAR(18), WORKLOAD INT)

    COMMIT;

    INSERT INTO PERSON VALUES('NIDM','ENGINEERING', 50);
    INSERT INTO PERSON VALUES('NIDM','IT', 50);


    This is OK:
    SELECT NAME, SUM(WORKLOAD) FROM PERSON
    GROUP BY NAME;

    But this, got -120:
    SELECT NAME FROM PERSON
    WHERE SUM(WORKLOAD) <= 100
    GROUP BY NAME;

  2. #2
    Join Date
    Dec 2002
    Posts
    134
    Use having:

    SELECT NAME FROM PERSON
    GROUP BY NAME
    HAVING SUM(WORKLOAD) <= 100

  3. #3
    Join Date
    May 2003
    Posts
    113
    Thanks a lot

    Quote Originally Posted by chuzhoi
    Use having:

    SELECT NAME FROM PERSON
    GROUP BY NAME
    HAVING SUM(WORKLOAD) <= 100

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •