Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2003
    Posts
    21

    Unanswered: New column in where clause

    I have written the following query and it works perfectly....

    SELECT d .ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME, p.DEPARTMENT, COUNT(*) AS NoOfCalls, AVG(d .DURATION) AS AverageLength, MAX(d .DURATION) AS MaxCall, SUM(d .DURATION) AS TotalMins, SUM(CASE WHEN d .duration < 1 THEN 0.04 ELSE (0.04 + ((d .duration - 1) * 0.04)) END) AS Cost

    FROM DETAIL d INNER JOIN PHONE_EMP_MAST p

    ON d .ACCOUNT_CODE = p.ACCOUNT_CODE

    GROUP BY d .ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME, p.DEPARTMENT


    I want to put in a clause so that it will show only the records where average length is greater than 5.... how can you refer to this column in the where clause?

    When i put in a line saying where averagelength > '5', I get an error saying "Invalid column name"....

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: New column in where clause

    Originally posted by kieranodwyer
    I have written the following query and it works perfectly....

    SELECT d .ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME, p.DEPARTMENT, COUNT(*) AS NoOfCalls, AVG(d .DURATION) AS AverageLength, MAX(d .DURATION) AS MaxCall, SUM(d .DURATION) AS TotalMins, SUM(CASE WHEN d .duration < 1 THEN 0.04 ELSE (0.04 + ((d .duration - 1) * 0.04)) END) AS Cost

    FROM DETAIL d INNER JOIN PHONE_EMP_MAST p

    ON d .ACCOUNT_CODE = p.ACCOUNT_CODE

    GROUP BY d .ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME, p.DEPARTMENT


    I want to put in a clause so that it will show only the records where average length is greater than 5.... how can you refer to this column in the where clause?

    When i put in a line saying where averagelength > '5', I get an error saying "Invalid column name"....
    You need to use "HAVING AVG(d.DURATION) > 5"

    Also, no quotes wanted round the 5!

  3. #3
    Join Date
    Feb 2003
    Posts
    21
    I put that in:

    SELECT d .ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME, p.DEPARTMENT, COUNT(*) AS NoOfCalls, AVG(d .DURATION) AS AverageLength,
    MAX(d .DURATION) AS MaxCall, SUM(d .DURATION) AS TotalMins, SUM(CASE WHEN d .duration < 1 THEN 0.04 ELSE (0.04 + ((d .duration - 1) * 0.04))
    END) AS Cost

    FROM DETAIL d INNER JOIN
    PHONE_EMP_MAST p ON d .ACCOUNT_CODE = p.ACCOUNT_CODE

    HAVING AVG(d .DURATION) > 5

    GROUP BY d .ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME, p.DEPARTMENT


    but it still isn't working... it says there is an error near the keyword GROUP... (I'm using SQL server if that makes any difference...

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I don't know SQL Server, but I'd try moving the HAVING clause to AFTER the GROUP BY clause.

  5. #5
    Join Date
    Feb 2003
    Posts
    21
    Thanks Tony... putting it at the end of the statement made it work!

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by kieranodwyer
    Thanks Tony... putting it at the end of the statement made it work!
    It makes sense - you have to GROUP the data before you can find the groups HAVING a certain property...
    ... having said that, Oracle actually doesn't mind which way round you do it.

Posting Permissions

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