“You tried to execute a query that does not include the specified aggregate function”

The error is because fName is included in the SELECT list, but is not included in a GROUP BY clause and is not part of an aggregate function (Count(), Min(), Max(), Sum(), etc.)

You can fix that problem by including fName in a GROUP BY. But then you will face the same issue with surname. So put both in the GROUP BY:

SELECT
    fName,
    surname,
    Count(*) AS num_rows
FROM
    author
    INNER JOIN book
    ON author.aID = book.authorID;
GROUP BY
    fName,
    surname

Note I used Count(*) where you wanted SUM(orders.quantity). However, orders isn’t included in the FROM section of your query, so you must include it before you can Sum() one of its fields.

If you have Access available, build the query in the query designer. It can help you understand what features are possible and apply the correct Access SQL syntax.

Related Posts:

  1. Why do we need “Relationships” between tables at all?
  2. How do you trouble shoot a “Data type mismatch in criteria expression” error in MS Access 2010?
  3. How to SUM two fields within an SQL query
  4. How do I count unique items in field in Access query?
  5. MySQL Error: : ‘Access denied for user ‘root’@’localhost’
  6. What is the difference between “INNER JOIN” and “OUTER JOIN”?
  7. SQL WITH clause example [duplicate]
  8. The wait operation timed out. ASP
  9. SQL query to select dates between two dates
  10. MySQL – Operand should contain 1 column(s)
  11. SQL SELECT WHERE field contains words
  12. MySQL Error: : ‘Access denied for user ‘root’@’localhost’
  13. NOT IN vs NOT EXISTS
  14. Must declare the scalar variable
  15. ORA-00979 not a group by expression
  16. How can I do a FULL OUTER JOIN in MySQL?
  17. MySQL query String contains
  18. How do I escape a single quote in SQL Server?
  19. Finding duplicate values in a SQL table
  20. MySQL Cannot Add Foreign Key Constraint
  21. Oracle error : ORA-00905: Missing keyword
  22. How do I import an SQL file using the command line in MySQL?
  23. mysql Foreign key constraint is incorrectly formed error
  24. Can a foreign key be NULL and/or duplicate?
  25. How do composite indexes work?
  26. Difference between JOIN and INNER JOIN
  27. MySQL “WITH” clause
  28. What is the difference between JOIN and UNION?
  29. MySQL Multiple Joins in one query?
  30. Error Code: 2013. Lost connection to MySQL server during query
  31. “CASE” statement within “WHERE” clause in SQL Server 2008
  32. SQL join on multiple columns in same tables
  33. ORA-00972 identifier is too long alias column name
  34. What is the difference between Scope_Identity(), Identity(), @@Identity, and Ident_Current()?
  35. Sql query – getting rid of hard-coded values
  36. ORA-00918: column ambiguously defined in SELECT *
  37. How To Run A Github Repository?
  38. SQL Switch/Case in ‘where’ clause
  39. Oracle – ORA-01489: result of string concatenation is too long [duplicate]
  40. SQL Server WITH statement
  41. How to run a SQL query on an Excel table?
  42. Algebra Relational sql GROUP BY SORT BY ORDER BY
  43. SQL Server reports ‘Invalid column name’, but the column is present and the query works through management studio
  44. Oracle “Partition By” Keyword
  45. What is the equivalent of ‘describe table’ in SQL Server?
  46. Add a column with a default value to an existing table in SQL Server
  47. Remote table-Valued Function Calls are not allowed
  48. The backend version is not supported to design database diagrams or tables
  49. Determine ROW that caused “unexpected end of file” error in BULK INSERT?
  50. SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax — PHP — PDO [duplicate]
  51. Difference between View and table in sql
  52. Using group by on multiple columns
  53. What is the purpose of using WHERE 1=1 in SQL statements?
  54. Exclude a column using SELECT * [except columnA] FROM tableA?
  55. How do you force mysql LIKE to be case sensitive?
  56. What is the difference between a stored procedure and a view?
  57. Bulk load data conversion error (truncation)
  58. Insert into a MySQL table or update if exists
  59. Convert timestamp to date in Oracle SQL
  60. How to order by with union in SQL?
  61. Update statement with inner join on Oracle
  62. PostgreSQL create table if not exists
  63. Incorrect syntax near ”
  64. MySQL: Can’t create table (errno: 150)
  65. Join vs. sub-query
  66. Alternative to except in MySQL
  67. DATEDIFF function in Oracle
  68. GROUP BY and COUNT using ActiveRecord
  69. MySQL – Get row number on select
  70. What does * mean in sql?
  71. How to truncate the text returned for a column in a MySQL query
  72. INSERT VALUES WHERE NOT EXISTS
  73. Using union and order by clause in mysql
  74. Difference between `load data inpath ` and `location` in hive?
  75. How to implement LIMIT with SQL Server? [duplicate]
  76. What’s the best way to dedupe a table?
  77. How To Write An Inner Join With WP Query
  78. How do I escape a table name or column name in SQL? esc_sql doesn’t do this
  79. Update user_login, user_nicename, and display_name
  80. Change sticky status of posts from phpMyAdmin
  81. Fetch all Posts where logged in user has commented
  82. Needing to move content from postmeta to posts in sql [closed]
  83. How to import a Typo3 database to a wordpress site?
  84. What does the $posts_join filter join to?
  85. Clean up very big and very dirty database
  86. How to delete ALL comments from certain category in WordPress database?
  87. How can I convert everything from category X to have post format Link
  88. Bulk delete WordPress Post and all metadata, etc using SQL query
  89. What steps do I need to take to install a local copy of a live website?
  90. Reset post IDs with all post meta
  91. How to Add or Change Post Title
  92. Why does DROP TABLE-ing the `wp_options` reset my user session?
  93. How to refactor DB queries for better TTFB in WordPress?
  94. query sql-table and change entities
  95. Duplicate WP Migration affecting site on separate domain?
  96. Analog category_and (WP) in sql query
  97. Backtick (MySQL norm) added to SQL Server Query causing error
  98. SQL query to check whether a meta key is set or not for a post in post_meta table
  99. Firebase with WordPress instead of SQL?
  100. Want to delete woocommerce coupon in bulk from phpmyadmin based on published date

Leave a Comment