Fetch rows with count

Question

--------------------------------------------------
| UserID    |   Name        | RefID              |
--------------------------------------------------
| 1         | xyz           |  1                 |
| 2         | abc           |  1                 |
| 3         | def           |  1                 |
| 4         | gij           |  2                 |
| 5         | jkl           |  2                 |
| 6         | mno           |  2                 |
--------------------------------------------------

$query = mysql_query("SELECT * FROM `users` WHERE UserID='$id' LIMIT 1 ");
$query_ref = mysql_query("SELECT COUNT(ref) FROM `users` WHERE RefID='$id'");

Can these two queries be merged into one query?

Answer 1

Are you looking for something like this?

SELECT UserID, Name,
(
  SELECT COUNT(RefID) 
    FROM users 
   WHERE RefID = u.UserID
) RefCount
  FROM users u
 WHERE UserID = ?
 LIMIT 1

Sample output:

| USERID | NAME | REFCOUNT |
|--------|------|----------|
|      1 |  xyz |        3 |

Here is SQLFiddle demo

On a side note: learn and use prepared statements with either Mysqli or PDO instead of interpolating query strings.

Answer 2

From what I can understand you want to count number of users with a reference id. I would use this way:

SELECT count(*) as "counted" FROM users WHERE RefID=1

This will giff you the data you might want.

Regards