I am developing a system that tracks phonecall usage. I have an sql server db that includes two tables which include (amongst others) the fields below them:

phone_emp_mast (contains user info.)
---------------------
Account_Code
First_Name
Last_Name
Department

detail (contains info on individual calls)
-------
Account_Code
Duration

I want to write a query that returns the account_code, first_name, last_name, no. of calls made, avg. length of call, max. length of call and total minutes of calls for each employee in a given department

I tried the following query:

SELECT d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME, COUNT(*) AS NoOfCalls, AVG(d.DURATION) AS AverageLength, MAX(d.DURATION) AS MaxCall, SUM(d.DURATION) AS TotalMins

FROM DETAIL d CROSS JOIN
PHONE_EMP_MAST p

WHERE (d.ACCOUNT_CODE = d.ACCOUNT_CODE) AND (p.DEPARTMENT = 'deptname')
GROUP BY d.ACCOUNT_CODE;

and it keeps returning the error message:
Column p.last_name is invalid in the select list because it is not contained in either an aggregate function or the group by clause

Can anyone figure out a better way of doing this? please?

The GROUP BY clause has to specify every column that was not aggregated in the SELECT clause - irritatingly!

SELECT d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME, COUNT(*) AS NoOfCalls, AVG(d.DURATION) AS AverageLength, MAX(d.DURATION) AS MaxCall, SUM(d.DURATION) AS TotalMins

FROM DETAIL d CROSS JOIN
PHONE_EMP_MAST p

WHERE (d.ACCOUNT_CODE = d.ACCOUNT_CODE) AND (p.DEPARTMENT = 'deptname')
GROUP BY d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME;

Thanks for that, I have used the following select statement and it is working perfectly. However I now have a new problem...


SELECT d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME, COUNT(*) AS NoOfCalls, AVG(d.DURATION) AS AverageLength, MAX(d.DURATION) AS MaxCall,
SUM(d.DURATION) AS TotalMins
FROM DETAIL d INNER JOIN
PHONE_EMP_MAST p ON d.ACCOUNT_CODE = p.ACCOUNT_CODE
WHERE (p.DEPARTMENT = 'deptname')
GROUP BY d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME
ORDER BY p.LAST_NAME;


I now need to get a count of the number of fields in the detail table where the duration is less than a minute... can anyone suggest a way of doing this. I tried putting in the bits between the stars

SELECT d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME, COUNT(*) AS NoOfCalls, AVG(d.DURATION) AS AverageLength, MAX(d.DURATION) AS MaxCall,
SUM(d.DURATION) AS TotalMins,
***(SELECT COUNT(d.DURATION)
WHERE duration < 1) AS NoOfCallsLessThan1***
FROM DETAIL d INNER JOIN
PHONE_EMP_MAST p ON d.ACCOUNT_CODE = p.ACCOUNT_CODE
WHERE (p.DEPARTMENT = 'operations admin')
GROUP BY d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME, ***d.DURATION, p.ACCOUNT_CODE***
ORDER BY p.LAST_NAME

but when I do this it returns all the calls individually since instead of only returning 1 row for each account code... anyone have any suggestions or is there anything you don't understand about my explanation?

Thanks in advance!

You need to add a CASE (or DECODE if using Oracle pre-9i) expression in your first query:

SELECT d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME, 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 1 ELSE 0 END ) shortcalls
FROM DETAIL d INNER JOIN
PHONE_EMP_MAST p ON d.ACCOUNT_CODE = p.ACCOUNT_CODE
WHERE (p.DEPARTMENT = 'deptname')
GROUP BY d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME
ORDER BY p.LAST_NAME;

The DECODE version would be:

SUM( DECODE( SIGN( d.duration-1), -1, 1, 0 ) ) shortcalls

I'm actually using SQL server... do you know if SQL server supports this function?

Not DECODE - that's Oracle's own, but CASE is ANSI standard so I imagine that at least the latest version of SQL Server supports it.

Thanks alot Tony... Thats working perfectly!