help with simple SQL problem please

Discussion in 'OT Technology' started by peytonrm, Nov 6, 2006.

  1. peytonrm

    peytonrm New Member

    Joined:
    Feb 8, 2005
    Messages:
    8
    Likes Received:
    0
    What I need to do is return the name and address of customers with 3 or more complaints reported. The name and address is in a REPORTER table, with a REPORTS table linking them with the details about their complaint. Here is what I tried:
    Code:
    SELECT REPORTER.ReporterNameFirst, REPORTER.ReporterNameLast, REPORTER.ReporterAddressStreet, REPORTER.ReporterAddressCity, REPORTER.ReporterAddressState, REPORTER.ReporterAddressZip, Count(*) 
    FROM REPORTER, REPORTS
    WHERE REPORTER.ReporterID=REPORTS.ReporterID
    GROUP BY REPORTS.ReporterID
    HAVING Count(*)>=3;
    When I try to execute it, I get "you tried to execute a query that does not include the specified field ReporterNameFirst as part of an aggregate function" . I'm a newb, so hopefully this is an easy fix.

    This is due tomorrow, so any help would be greatly appreciated.
     
  2. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Stick all the reporter. fields in the group by clause. You can't specify a field without either grouping it, or summing it, or counting it... etc. You've also grouped by a field you didn't select (reporterid).
     
  3. peytonrm

    peytonrm New Member

    Joined:
    Feb 8, 2005
    Messages:
    8
    Likes Received:
    0
    We have a winner. I, and my GPA, thank you.
     

Share This Page