Results 1 to 12 of 12
  1. #1
    Tyork is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Oct 2010
    Posts
    35

    SQL Count and Group By

    I'm working with a table named:

    dbo_Call_History

    Relevant fields are:



    'phone' - This is a phone number
    'status' - this is a character code examples would be 'PY', 'CN', 'T'

    My goal is to group by phone and get a count of each status. I can't quite wrap my head around this. Any help would be appreciated.

  2. #2
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    select count(*),phone,status from dbo_call_history group by phone,status

  3. #3
    Tyork is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Oct 2010
    Posts
    35
    I'd like to take it a step further and list only 'phone' where there is NO status of: 'CN', 'PY', 'DI', 'AM', 'TC', 'NO', 'NR', 'NM', 'CB', 'T'

    Then i'd like a count of 'phone' that meets the criteria above

    My goal is to get a list of accounts where no contacts have been made (the status codes above indicate contact). Then I'd like to see how many attempts have been made to that particular number.

  4. #4
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    select count(*),phone,status from dbo_call_history where status is null group by phone,status

  5. #5
    Tyork is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Oct 2010
    Posts
    35
    That will not work because there may be 100 records associated with a phone number. 1 of which may have a status indicating a contact. This query would still count the other 99 and list the phone number as never having a contact.

    I only want the phone number to show up if there has never been a contact associated with it. Then and only then I want a count of the times the number has been called.

  6. #6
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    what do you mean by "has never been a contact associated with it"?
    can you explain more on the field "status"?

  7. #7
    Tyork is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Oct 2010
    Posts
    35
    Sure.

    So the table I'm working with lists the results of all outbound calls made by a company. Each call or attempt results in a status which indicates if a contact has been made or if we got an answering machine etc. basically what the outcome of that particular attempt is.

    I'm trying to compile a list of phone numbers that we have called where we never made a "contact" which is indicated by one of the following statuses:

    'CN', 'PY', 'DI', 'AM', 'TC', 'NO', 'NR', 'NM', 'CB', 'T'

    Keep in mind if there wasnt a "contact" the status will be something else and not NULL.

    I then want to see the total number of calls made to each of those phone numbers.

  8. #8
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    select count(*),phone,status from dbo_call_history where status not in ('CN', 'PY', 'DI', 'AM', 'TC', 'NO', 'NR', 'NM', 'CB', 'T') group by phone,status

  9. #9
    Tyork is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Oct 2010
    Posts
    35
    But that would be the same as using multiple OR statements for the WHERE statement. It will still result in phone numbers that have received calls where a contact was made. It will just exclude the count of attempts that were "contacts" from the count total.

    I think what I need is a query that:

    1. Groups by phone and aggregates the count of each status in a Status field header

    It would look like:

    Phone l CN l PY l DI l AM l TC l NO l NR l NM l CB l T ....... and so on




    The count of each status grouped by phone number would lie underneath

    2. Select only the phones where the sum of the contact fields are 0 or WHERE they all = 0.

    I'm not sure how to go about executing this. Does that make sense?

  10. #10
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    it's call PIVOT query:

    TRANSFORM Count(dbo_call_history.phone) AS CountOfphone
    SELECT dbo_call_history.phone
    FROM dbo_call_history
    WHERE (((dbo_call_history.status) Not In ('CN','PY','DI','AM','TC','NO','NR','NM','CB','T') ))
    GROUP BY dbo_call_history.phone
    PIVOT dbo_call_history.status;

  11. #11
    Tyork is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Oct 2010
    Posts
    35
    This is what I was looking for:

    Code:
     
    SELECT p.phone, count(*)
    FROM Call_History P
    LEFT JOIN
    (
    SELECT Phone
    FROM Call_History
    WHERE Status IN('CN','PY','DI','AM','TC','NO','NR','NM','CB','T')
    GROUP BY Phone) t
    ON
    p.phone = t.phone
    WHERE t.phone is null
    GROUP BY p.phone

  12. #12
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    Good work.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query to group and count field entries
    By newtoAccess in forum Queries
    Replies: 4
    Last Post: 11-29-2010, 09:19 AM
  2. Replies: 5
    Last Post: 11-29-2010, 08:16 AM
  3. Group by
    By EstesExpress in forum Queries
    Replies: 0
    Last Post: 02-26-2010, 07:52 AM
  4. Replies: 2
    Last Post: 11-18-2009, 06:49 PM
  5. columns for group detail but not group header?
    By Coolpapabell in forum Reports
    Replies: 0
    Last Post: 08-21-2009, 08:53 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums