How to select random row with count() Table names:

g_number  name
123       John
123       Sara
123       Michael


I want to get one RANDOM name and quantity of all names with the same g_number in one query:

SELECT name, count(*) cnt
FROM names
WHERE g_number='123'
ORDER BY rand()


But result is always the same

name    cnt
John    3


Looks like count() ignores ORDER BY rand() line.
So that's the question: is it possible to solve this in ONE query?
I think you want to do two different things in one query: 


randomly choose one name out of all the ones `WHERE g_number=123 
count all the rows WHERE g_number=123.


If that's what you want to do, do it with two subqueries:

SELECT
   (
     SELECT name
     FROM t
     WHERE g_number = 123
     ORDER BY rand()
     LIMIT 1
   ) AS name
   , (
     SELECT count(*)
     FROM t
     WHERE g_number = 123
   ) AS count_of_123


That will give you as a result:

|    name | count_of_123 |
|---------|--------------|
|    Sara |            3 |


or 

|    name | count_of_123 |
|---------|--------------|
| Michael |            3 |


or

|    name | count_of_123 |
|---------|--------------|
|    John |            3 |


You can check it at http://sqlfiddle.com/#!9/5342d2/8/0

This is not just MySQL, it is standard SQL (except, maybe, for the name of the rand() function, that might be DBMS-dependent). The query also reflects clearly the fact that you want to do two (related but different) things. You can check also at SQLFiddle the PostgreSQL version, and the SQLite one.



Within the realm of MySQL, you can use one non-standard query that will also work (and which, performance-wise, is probably better):

SELECT
   name, count(*) AS count_of_123
FROM
   (
     SELECT name
     FROM t
     WHERE g_number = 123
     ORDER BY rand()
   ) AS q0 ;


I think this is closer to what the original poster had in mind. Even so, I wouldn't rely on it. This is very far from standard SQL. MySQL might decide to change how this is implementd and behaves in the future. Also, IMHO, it is less clear what the intent of the query is.

Check it at http://sqlfiddle.com/#!9/5342d2/10/0