title: SELECT from multiple tables with GROUP BY
body: <p>I've a got a little problem with multitable query. (RDBMS: Access)</p>
  
  <p>Here is the database schema: (only S_Cards, Books, Authors, Students tables are used in this query)
  S_Cards is Student book order (in library).</p>
  
  <p><img src="https://i.stack.imgur.com/fgg6u.jpg" alt="DB Scheme"></p>
  
  <p>Query:
  Select the most popular author(s) among students and the number of this author's books, which were ordered in library.</p>
  
  <p>Although I can get list of orders + authors like this in one query:</p>
  
  <pre><code>SELECT 
      Students.FirstName &amp; " " &amp; Students.LastName AS [Student], 
      Books.Name AS [Book], Authors.FirstName &amp; " " &amp; Authors.LastName AS [Author]
  FROM 
      Students, 
      Books, 
      S_Cards, 
      Authors
  WHERE 
      S_Cards.ID_Student = Students.ID 
  AND S_Cards.ID_Book = Books.ID 
  AND Books.ID_Author = Authors.ID
  ORDER BY Authors.LastName
  </code></pre>
  
  <p>Result (sorry, it's in Russian):</p>
  
  <p><img src="https://i.stack.imgur.com/HwttL.jpg" alt="Query result"></p>
  
  <p>I can't figure out, why I can't COUNT and GROUP BY like this:</p>
  
  <pre><code>SELECT 
      Students.FirstName &amp; " " &amp; Students.LastName AS [Student], 
      Books.Name AS [Book], 
      COUNT(Authors.FirstName &amp; " " &amp; Authors.LastName) AS [Number of books]
  FROM Students, Books, S_Cards, Authors
  WHERE 
      S_Cards.ID_Student = Students.ID 
  AND S_Cards.ID_Book = Books.ID 
  AND Books.ID_Author = Authors.ID
  GROUP BY 3
  </code></pre>
  
  <p>I get an error that 'Authors.FirstName &amp; " " &amp; Authors.LastName' is not a part of static function or group.</p>
  
  <p>Questions:</p>
  
  <ol>
  <li>Is there a way to do this query without JOIN, only by GROUP BY, SELECT, UNION and how?</li>
  <li>What's the problem in my 2nd query?</li>
  </ol>