user5779223 user5779223 - 6 months ago 15
MySQL Question

Fail to select with count, group by, order by and union in Mysql

I have the following three tables:

theUser(*user_id*, user_name)
ichat(*message_id*, sender, receiver, send_time)
gchat(*message_id*, sender, receiver, send_time)


The only difference between ichat and gchat is that the
receiver
in ichat is user and it is group in
gchat
. But it doesn't effect in this question.

Now I'd like to find out the most active
n
users (the user name and corresponding number of messages sent), that means who appears most frequently in
sender
. And following is the code I have tried:

SELECT COUNT(totalM.*) AS msge, u.name
FROM (
SELECT * FROM gchat
UNION ALL
SELECT * FROM ichat) AS totalM
JOIN theUser u ON totalM.sender=u.user_id
GROUP BY totalM.sender
ORDER BY COUNT(*)


But got such an error:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '*) AS msge, u.name.


Do you have any idea about it? Thank you in advance!

Answer
 SELECT COUNT(*) AS msge, u.name
    FROM (
        SELECT * FROM gchat
        UNION ALL
        SELECT * FROM ichat) AS totalM
    JOIN theUser u ON totalM.sender=u.user_id
    GROUP BY u.Name
    ORDER BY COUNT(*)
  1. GROUP BY should match non-aggregated select columns
  2. I believe the engine can't count one side of a table join. It doesn't know the relationship is 1-1 or 1-M before it starts counting so it has to count both sides thus count(*).

Or you could just switch both count(*)'s to count(1) for the same effect I belive.