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

    Unanswered: Data from two tables

    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?

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

    Re: Data from two tables

    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;

  3. #3
    Join Date
    Feb 2003
    Posts
    21
    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!

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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

  5. #5
    Join Date
    Feb 2003
    Posts
    21
    I'm actually using SQL server... do you know if SQL server supports this function?

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by kieranodwyer
    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.

  7. #7
    Join Date
    Feb 2003
    Posts
    21
    Thanks alot Tony... Thats working perfectly!

Posting Permissions

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