Questions tagged [mysql-error-1111]

MySql Error #1111 - Invalid use of group function

If you try to use an aggregated function in a context where a non-aggregated function is expected, you'll get this error:

MySql Error #1111 - Invalid use of group function

As an example, this query will raise error 1111:

SELECT id, COUNT(*)
FROM yourtable
WHERE COUNT(*) > 1
GROUP BY id

since the WHERE clause is evaluated before the GROUP BY, the COUNT(*) is not known in this context. In this example you have to use HAVING clause instead:

SELECT id, COUNT(*)
FROM yourtable
GROUP BY id
HAVING COUNT(*) > 1
87 questions
163
votes
5 answers

SQL Group By with an Order By

I have a table of tags and want to get the highest count tags from the list. Sample data looks like this id (1) tag ('night') id (2) tag ('awesome') id (3) tag ('night') using SELECT COUNT(*), `Tag` from `images-tags` GROUP BY `Tag` gets me back…
maxsilver
  • 4,524
  • 4
  • 28
  • 24
128
votes
4 answers

MySQL: Invalid use of group function

I am using MySQL. Here is my schema: Suppliers(sid: integer, sname: string, address string) Parts(pid: integer, pname: string, color: string) Catalog(sid: integer, pid: integer, cost: real) (primary keys are bolded) I am trying to write a query to…
Nick Heiner
  • 119,074
  • 188
  • 476
  • 699
34
votes
5 answers

Update a column with a COUNT of other fields is SQL?

I have the following tables set up: Articles: ID | TITLE | CONTENT | USER | NUM_COMMENTS COMMENTS ID | ARTICLE_ID | TEXT I need a sql statement which updates the NUM_Comments field of the articles table with teh count of the comments made against…
Ali
  • 7,353
  • 20
  • 103
  • 161
25
votes
2 answers

Error Code 1111. Invalid use of group function

So this works: SELECT c.name AS country_name, c.population AS country_population, SUM(ci.population) AS city_population, ROUND(100*(SUM(ci.population)/c.population)) AS city_population_percent FROM country AS c JOIN city AS ci ON c.code =…
user3374108
  • 263
  • 1
  • 3
  • 4
16
votes
2 answers

mysql query to update field to max(field) + 1

What I want to do is: UPDATE table SET field = MAX(field) + 1 WHERE id IN (1, 3, 5, 6, 8); The semantics of this statement, in my mind, would be first the database would go off and determine for me what the largest value of field is in all of…
vicatcu
  • 5,407
  • 7
  • 41
  • 65
9
votes
4 answers

MySQL: UPDATE with a JOIN and a GROUP_CONCAT

Is this possible? I have 2 tables, Customers and Orders. Now I want to fill a column in Customers with all order id's of that customer (comma separated). I tried something like this, but it doesnt work: UPDATE customers AS c LEFT JOIN orders AS o…
Dylan
  • 9,129
  • 20
  • 96
  • 153
7
votes
3 answers

SQL Query to Select Everything Except the Max Value

I have this rather complex query that grabs data from three tables, and now I want it to be even more complicated (Oh dear)! I'd like the last posted feature to be displayed in it's own section of the page, and that's pretty easy by selecting the…
different
  • 2,343
  • 3
  • 24
  • 30
6
votes
1 answer

ActiveRecord syntax for finding all items with an average rating of x or greater when using a join

I have two models in my rails application. Items and Reviews. Reviews belong to Item and Items has many Reviews. The review model looks like this: create_table "reviews", :force => true do |t| t.text "comment" t.integer "rating" t.integer…
KJF
  • 2,083
  • 4
  • 21
  • 38
5
votes
4 answers

mysql where count(column_name) = 1?

Here is the query I am using: SELECT k_id, COUNT(k_id) AS k_count FROM template_keyword_link WHERE k_id IN(1,2,3,4,5) GROUP BY k_id; This query returns something like 1 | 6 2 | 1 3 | 4 4 | 1 5 | 9 I want to add something like AND COUNT(k_id) =…
Hailwood
  • 89,623
  • 107
  • 270
  • 423
4
votes
2 answers

#1111 - Invalid use of group function

I am using the following query in an attempt to get  total number(sum) of slides retrieving the max number from each project, however I am receiving the following error (#1111 - Invalid use of group function). Here's the query: SELECT…
azsl1326
  • 1,410
  • 2
  • 13
  • 23
4
votes
3 answers

CakePHP: How do I count the number of hasMany records in a find?

I have two models, Post hasMany Comment. How do I select all Post that have less than two Comment? I tried using a find with 'fields'=>array('COUNT(Comment.id) as numComments','Post.*'), (and then doing a numComments < 2 in 'conditions'). But, I get…
atp
  • 30,132
  • 47
  • 125
  • 187
3
votes
1 answer

simple SQL query giving Invalid use of group function

Can anyone tell me why I'm getting Invalid use of group function and how to stop it? SELECT Name, Message FROM flux_chat_messages WHERE id >= ( MAX( id ) -5 ) ORDER BY id ASC
CyanPrime
  • 5,096
  • 12
  • 58
  • 79
3
votes
2 answers

MySQL: How to update SUM() values of children to a parent row in the same table?

I need to get the sum values of my child rows and update that data on the parent row. I've got this for as shown below but ran into error #1111 (Invalid use of group function). CREATE TEMPORARY TABLE cms_ladu_temp LIKE cms_ladu; INSERT INTO…
Jaak Kütt
  • 2,566
  • 4
  • 31
  • 39
2
votes
1 answer

SQL: order by count from different table with condition

I want to display the results such that: record number is ordered by the number of items attached to it IF the number of items attached is greater than 100 SELECT r.number, r.title, COUNT(i.itemnumber) FROM record r LEFT JOIN items i…
Bubnoff
  • 3,917
  • 3
  • 30
  • 33
2
votes
3 answers

sql query: how to make the parents without children?

I have this query to get the first result I want from this post earlier, SELECT parents.tag_id AS ParentID, parents.tag_name AS ParentName, COUNT(childs.tag_id) AS TotalChildren FROM root_tags AS parents LEFT OUTER JOIN root_tags…
Run
  • 54,938
  • 169
  • 450
  • 748
1
2 3 4 5 6