MySQL: Invalid use of group function

You need to use HAVING, not WHERE.

The difference is: the WHERE clause filters which rows MySQL selects. Then MySQL groups the rows together and aggregates the numbers for your COUNT function.

HAVING is like WHERE, only it happens after the COUNT value has been computed, so it’ll work as you expect. Rewrite your subquery as:

(                  -- where that pid is in the set:
SELECT c2.pid                  -- of pids
FROM Catalog AS c2             -- from catalog
WHERE c2.pid = c1.pid
HAVING COUNT(c2.sid) >= 2)

Related Posts:

  1. MySQL Error: : ‘Access denied for user ‘root’@’localhost’
  2. Unknown column in ‘field list’ error on MySQL Update query
  3. Unknown column in ‘field list’ error on MySQL Update query
  4. MySQL – Operand should contain 1 column(s)
  5. MySQL “CREATE TABLE IF NOT EXISTS” -> Error 1050
  6. MySQL Error: : ‘Access denied for user ‘root’@’localhost’
  7. MySQL create table if not exists and insert record only if table was created
  8. How do I use properly CASE..WHEN in MySQL
  9. How can I do a FULL OUTER JOIN in MySQL?
  10. MySQL query String contains
  11. What’s the difference between VARCHAR and CHAR?
  12. Case statement in MySQL
  13. How to declare a variable in MySQL?
  14. MySQL Cannot Add Foreign Key Constraint
  15. How to split the name string in mysql?
  16. How do I import an SQL file using the command line in MySQL?
  17. Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)
  18. mysql Foreign key constraint is incorrectly formed error
  19. How can I return pivot table output in MySQL?
  20. Compare dates in MySQL
  21. MySQL “WITH” clause
  22. MySQL Multiple Joins in one query?
  23. Error Code: 2013. Lost connection to MySQL server during query
  24. MySQL syntax for Join Update
  25. Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)
  26. how to drop partition without dropping data in MySQL?
  27. MySQL: Invalid use of group function
  28. MySQL combine two columns into one column
  29. Simple way to calculate median with MySQL
  30. Error 1046 No database Selected, how to resolve?
  31. MySQL Error 1264: out of range value for column
  32. Remove duplicate rows in MySQL
  33. How to SUM and SUBTRACT using SQL?
  34. Cast from VARCHAR to INT – MySQL
  35. SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax — PHP — PDO [duplicate]
  36. How do you force mysql LIKE to be case sensitive?
  37. MySQL – UPDATE multiple rows with different values in one query
  38. How to create a MySQL hierarchical recursive query?
  39. ERROR 1148: The used command is not allowed with this MySQL version
  40. Insert into a MySQL table or update if exists
  41. How to round down to nearest integer in MySQL?
  42. MySQL starts with searching issue
  43. Query to convert from datetime to date mysql
  44. Is it possible to GROUP BY multiple columns using MySQL?
  45. MySQL error: key specification without a key length
  46. Best way to test if a row exists in a MySQL table
  47. Check if MySQL table exists without using “select from” syntax?
  48. MySQL equivalent of DECODE function in Oracle
  49. mysql update column with value from another table
  50. The used SELECT statements have a different number of columns
  51. How to delete from multiple tables in MySQL?
  52. MySQL: Can’t create table (errno: 150)
  53. How to copy a row and insert in same table with a autoincrement field in MySQL?
  54. Join vs. sub-query
  55. Alternative to except in MySQL
  56. Equivalent of explode() to work with strings in MySQL
  57. ROW_NUMBER() in MySQ
  58. MySQL OPTIMIZE all tables?
  59. MySQL INSERT INTO table VALUES.. vs INSERT INTO table SET
  60. MySQL – Get row number on select
  61. MySQL error: Unknown column in ‘where clause’
  62. How to truncate the text returned for a column in a MySQL query
  63. Using union and order by clause in mysql
  64. MySQL LIKE IN()?
  65. How to create Temp table with SELECT * INTO tempTable FROM CTE Query
  66. Using group by on multiple columns
  67. Efficiently convert rows to columns in sql server
  68. Nested select statement in SQL Server
  69. Sql Server equivalent of a COUNTIF aggregate function
  70. SQL Query Where Field DOES NOT Contain $x
  71. How can I select the first day of a month in SQL?
  72. Warning: Null value is eliminated by an aggregate or other SET operation in Aqua Data Studio
  73. Oracle Apex column link from LOV values
  74. ORA-01861: literal does not match format string
  75. SQL Server Insert if not exists
  76. SQL Server: Invalid Column Name
  77. Unknown Column In Where Clause
  78. How do I reset a sequence in Oracle?
  79. SQL: Two select statements in one query
  80. What does Include() do in LINQ?
  81. SQL Server “cannot perform an aggregate function on an expression containing an aggregate or a subquery”, but Sybase can
  82. Get everything after and before certain character in SQL Server
  83. SQLite string contains other string query
  84. “#1067 – Invalid default value for ‘post_date'” when trying to reset AI after backup
  85. Inserting Post Meta From SQL
  86. WordPress SQL LIKE request doesn’t work for fields with special symbols
  87. SQL command to convert all tags in lowercase?
  88. How can I add a new row in a separate database when someone registers via WordPress?
  89. WordPress can I manually add columns in users table and display it in the default manage user plugin and registration form? [closed]
  90. WP MultiSite: Can’t select database error after changing to new domain
  91. $wpdb->get_var – What value is returned when zero records are found?
  92. Why is my insert row only inserting the final row from the loop into the database rather than just inserting one
  93. Database SQL query error
  94. Inserting other fields to existing registration form in a WordPress theme
  95. Organising and completing posts (mark as read and hide)
  96. can’t delete a row from post_meta table
  97. SQL error on restoring database
  98. Creating a “forum” – showing last post or last commented post
  99. If meta_key exists then don’t select this post?
  100. Possible SQL injection. How to locate and fix?

Leave a Comment