Select grouped by column only, not the aggregate

Question

In a MySql select statement involving aggregation, is it possible to select just the grouped by column without the aggregate?

Basically I want to select IDs in subquery according to a criteria based on an aggregate, in this case the total payments to a client:

select idclient, business_name from client where idclient in
(
  select idclient, sum(amount) as total 
  from payment 
  group by idclient
  having total > 100
)

... but this fails with error Operand should contain 1 column(s) because the subquery selects both the id (which I want) and the total (which I don't). Can I exclude total from the subquery result in any way?

Edit: if possible I would prefer to avoid using a join - the where clause is being passed onto another existing function on its own.

Apologies if this is a dupe - I did search, honest. I couldn't find an exact answer in the mass of SQL aggregate questions.

score 3 · Accepted Answer · answered Mar 31 '11 at 03:19

3

Your query should be like this:

select idclient, business_name from client where idclient in
(
  select idclient 
  from payment 
  group by idclient
  having sum(amount) > 100
)

You need to put aggregate function in having clause and in sub query you need to select # of columns same as in your where clause.

answered Mar 31 '11 at 03:19

anubhava

761,203
64
569
643

thanks! I don't know where I got the idea that 'having' could only use selected columns :\ – Rob Agar Mar 31 '11 at 03:39

score 1 · Answer 2 · answered Mar 31 '11 at 03:21

WHERE idclient IN (...)

The stuff inside (...) is a subquery. Obviously it should only return one column, because you only need one column of data for the IN clause.

You can omit the total column by:

SELECT idclient
FROM payment
GROUP BY idclient
HAVING SUM(amount) > 100

score 0 · Answer 3 · answered Mar 31 '11 at 12:41

You could also try this one:

SELECT c.idclient, c.business_name
FROM payment p
  INNER JOIN client c ON c.idclient = p.idclient
GROUP BY c.idclient, c.business_name
HAVING SUM(p.amount) > 100

And, because the client.idclient column looks very much like a PK, you could probably even omit client.business_name from GROUP BY. So the final query would look like this:

SELECT c.idclient, c.business_name
FROM payment p
  INNER JOIN client c ON c.idclient = p.idclient
GROUP BY c.idclient
HAVING SUM(p.amount) > 100

Select grouped by column only, not the aggregate

3 Answers3

Linked