title: SQL Subquery or Group BY to return Distinct values of a certain column?
body: <p>I am running into an issue where I know I am close to a solution but I cannot get the right data to come back or am getting errors on the queries. </p>
  
  <p>Basically what I want to do is to select values from a single table based on the following: I want them based on an employee name I supply and I want them to return DISTINCT Account Numbers for that employee(sometimes the same account number is listed more than one time...I only want one row of data from each duplicate account number). </p>
  
  <p>Here is what I have tried so far:  </p>
  
  <pre><code>SELECT DISTINCT * FROM Employees WHERE EmpName = 'Mary Johnson' GROUP BY AccountID 
  </code></pre>
  
  <p>-This returns an error saying I don't have an aggregate for the column EmployeeID(which isn't a calculated column, so why is it asking for an aggregate??)</p>
  
  <p>Then I tried a subquery:</p>
  
  <pre><code>SELECT EmployeeID, EmpName, Department, Position, (SELECT DISTINCT AccountID From Employees) AS AcctID FROM Employees WHERE EmpName = 'Mary Johnson'
  </code></pre>
  
  <p>-This gives me an error saying AT Most one record can be returned(I'm in Access).</p>
  
  <p>I know there has to be a solution to what I am looking for---Currently If I do a full query just on EmpName it returns 30 records, however, 23 of them have a duplicate AccountID, so there should only be 7 records that return with a DISTINCT AccountID. </p>
  
  <p>How can I achieve this via SQL?</p>
  
  <p>Here is an Example:</p>
  
  <pre><code>ID(PK)       EmpName          AcctID      Department   Position    EmpId
  ------       --------        ---------   --------     --------    ------
   1           Mary Johnson     1234          IT          Tech       226663
   2           Mary Johnson     1234          IT          Tech       226663
   3           Mary Johnson     1234          IT          Tech       226663
   4           Mary Johnson     2345          IT          Tech       226663
   5           John James       23442         Banking     Teller     445645
   6           Jame Tabor       1234          HR          Manager    234555
  </code></pre>
  
  <p>In the example above I want to do an SQL Query to get the rows back with the AccountId's for Mary Johnson that are DISTINCT.  So We would get back 2 rows  Mary Johnson 1234 and Mary Johnson 2345, ignoring the other 2 rows that Mary Johnson has with duplicate AccountIDs of 1234 and the other rows with employees not named Mary Johnson</p>