list contains nonaggregated column

Question

I have two tables that look like these:

users:
    id int unsigned auto_increment,
    primary key(id),
    [...]

product:
    id int unsigned auto_increment,
    seller id int unsigned auto_increment,
    primary key(id),
    foreign key(seller) references users(id)
    [...]

I want to have a list of users with all of their attributes and the amount of products each user has. I tried the following:

select u.id, [...], count(p.id) from users u, products p where p.seller=u.id and [...]

This, however gives me the following error:

ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'db.u.id'; this is incompatible with sql_mode=only_full_group_by

What would be the proper way of accomplishing the wanted result? And why does this not work?

Ok, got around my first question relatively quickly by using select id as userid, (select count(id) from products where id=userid) from users . I still don't understand the error. — l'arbre, Sep 04 '17 at 19:18
Error mentioned a group by. post actual query you have tried — Horaciux, Sep 04 '17 at 19:18

Radim Bača · Answer 1 · 2017-09-04T19:24:06.000

1

Use LEFT JOIN and GROUP BY to get the result you need. Moreover, all the attributes from user has to be part of GROUP BY clausula.

SELECT u.id,  [...], count(p.id) from users u
LEFT JOIN products p where p.seller=u.id
GROUP BY u.id, [...]

The error says that you can not mix aggregate functions and attributes behind SELECT if you do not have the GROUP BY clausula.

edited Sep 04 '17 at 19:24

answered Sep 04 '17 at 19:18

Radim Bača

10,646
1
19
33

score 1 · Answer 2 · answered Sep 04 '17 at 19:21

1

A correlated subquery might be the simplest approach:

select u.*,
       (select count(*) from products p where p.seller = u.id) as num_products
from users u;

answered Sep 04 '17 at 19:21

Gordon Linoff

1,242,037
58
646
786

score 1 · Accepted Answer · answered Sep 04 '17 at 21:34

You are aggregating data (count(p.id)). You are not using GROUP BY to get aggregates per some group, so you get only one result row with the aggregates.

But you also select u.id. Which one? There are many different u.id in the table, but you'll only get one result row. The DBMS expects you to tell it which one you want, e.g. MAX(u.id).

If you want to select user data, select from users. If you want to get the number of products, select them in a subquery (which is most easily done in the select clause, but can also be done in the from clause).

select
  u.*,
  (select count(*) from product p where p.seller = u.id) as number_of_products
from users u;

score 0 · Answer 4 · answered Sep 04 '17 at 19:19

0

Here is the correct query you can try:

ASCII

SELECT u.id, [...], COUNT(p.id) FROM users u JOIN products p ON u.id=p.seller GROUP BY p.seller;

NON-ASCII

SELECT u.id, [...], COUNT(p.id) FROM users u, products p WHERE u.id=p.seller GROUP BY p.seller;

answered Sep 04 '17 at 19:19

Jignesh M. Khatri

1,407
1
14
22

score 0 · Answer 5 · answered Mar 07 '20 at 18:18

Use ANY_VALUE for non aggregated columns. In your example, the non aggregated column was db.u.id (see the error message). So, you should use:

select ANY_VALUE(u.id), [...], count(p.id) 
from users u, products p 
where p.seller=u.id and [...]
group by [...]

More details here.

list contains nonaggregated column

5 Answers5