SQL GROUP BY multiple columns?

Question

I have one table and I need to group the entries by two different columns

here is my code

SELECT *
    FROM (
        SELECT max(user_msg.id) AS mid, max(user_msg.timestamp) AS tsp, user_msg.text, 
        usr1.id AS u1_id, usr1.nickname AS u1_nickname, usr1.avatar AS u1_avatar, usr1.avatar_art AS u1_avatar_art, 
        usr2.id AS u2_id, usr2.nickname AS u2_nickname, usr2.avatar AS u2_avatar, usr2.avatar_art AS u2_avatar_art,
        COUNT(user_msg.id) AS cnt
        FROM user_msg 
        join user using (client_id)
        LEFT JOIN user AS usr1 ON user_msg.from_id=usr1.id
        LEFT JOIN user AS usr2 ON user_msg.to_id=usr2.id
        WHERE user_msg.to_id = '".$user_id."' AND to_delete='0' OR user_msg.from_id = '".$user_id."' AND to_delete='0'
        group by u1_id, u2_id ORDER by tsp DESC
    ) c

it should be something like group by u1_id AND u2_id

This is not related to your question but I think you should Add some brackets around your conditions something like (user_msg.to_id = '".$user_id."' AND to_delete='0') OR (user_msg.from_id = '".$user_id."' AND to_delete='0') — cristian, Aug 07 '11 at 15:29

score 3 · Answer 1 · edited May 23 '17 at 11:47

You need to use the original column names, not the aliases. They aren't processed yet (well, in SQL Server they aren't: not sure of MYSQL).

...
   WHERE user_msg.to_id = '".$user_id."' AND to_delete='0' OR user_msg.from_id = '".$user_id."' AND to_delete='0'
  group by usr1.id, usr2.id
  ORDER by tsp DESC
...

Edit: MySQL allows aliases in the GROUP BY

So, I suspect the GROUP BY is wrong and ambiguous and need to be like the standard SQL underneath. With or without aliases

Like this question: SQL Query not showing expected result

[end edit]

Note, to make this standard SQL (or run on any other RDBMS), you need to use all the columns in the GROUP BY that are not in an aggregate:

    SELECT
       max(user_msg.id) AS mid, 
       max(user_msg.timestamp) AS tsp, 
       user_msg.text, 
       usr1.id AS u1_id, usr1.nickname AS u1_nickname, usr1.avatar AS u1_avatar, usr1.avatar_art AS u1_avatar_art, 
       usr2.id AS u2_id, usr2.nickname AS u2_nickname, usr2.avatar AS u2_avatar, usr2.avatar_art AS u2_avatar_art,
       COUNT(user_msg.id) AS cnt
    FROM user_msg 
    join user using (client_id)
    LEFT JOIN user AS usr1 ON user_msg.from_id=usr1.id
    LEFT JOIN user AS usr2 ON user_msg.to_id=usr2.id
    WHERE user_msg.to_id = '".$user_id."' AND to_delete='0' OR user_msg.from_id = '".$user_id."' AND to_delete='0'
    GROUP BY 
          user_msg.text, usr1.id, usr1.nickname, usr1.avatar, usr2.avatar_art,
          usr2.id, usr2.nickname, usr2.avatar, usr1.avatar_art
    ORDER by tsp DESC

And MySQL [does allow](http://dev.mysql.com/doc/refman/5.0/en/select.html#id931514) you to use aliases in GROUP BY: *A `select_expr` can be given an alias using `AS alias_name`. The alias is used as the expression's column name and can be used in `GROUP BY`, `ORDER BY`, or `HAVING` clauses.* — Andriy M, Aug 07 '11 at 16:18
@Andriy M: see my earlier edit (not the one where I added 3 columns) I checked MySQL docs and clarified it's a GROUP BY ambiguity rather than an alias problem http://stackoverflow.com/posts/6973710/revisions — gbn, Aug 07 '11 at 16:22

SQL GROUP BY multiple columns?

1 Answers1