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!

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

I don't know SQL Server, but I'd try moving the HAVING clause to AFTER the GROUP BY clause.

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.