Question: In my Events table I have a column called Pos1, which contains IDs from A to E. I need to count how many times 'E' appears in the column and where it ranks alongside the other numbers.

For example in this table 'E' occurs **1** time and is ranked **4** (D=3, A=2, C=2, E=1, B=0).

```
Pos1
E
C
D
C
D
D
A
A

```

I'm a complete SQL amateur so the closest I've got is an array printing the count of each ID, but I need this to be limited to counting a single ID *and* printing the rank.

```
$query = "SELECT Pos1, COUNT(Pos1) FROM Events GROUP BY Pos1"; 

$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)){

    echo $row['COUNT(Pos1)'] ."<br />";

}

```

Answer: 
try this:

```
SELECT Pos1, a.cnt, rank
FROM(
    SELECT a.Pos1, a.cnt, (@rank := @rank + 1) AS rank
    FROM(
        SELECT Pos1, COUNT(Pos1) cnt
        FROM Events
        GROUP BY Pos1
    ) a, (SELECT @rank := 0) b
    ORDER BY a.cnt DESC
)a
WHERE a.pos1 = 'E';

```

[SQLFIDDLE DEMO HERE](http://sqlfiddle.com/#!2/a1e4e/2)

EDIT: php code

```
$query = "SELECT Pos1, a.cnt, rank
          FROM(
            SELECT a.Pos1, a.cnt, (@rank := @rank + 1) AS rank
            FROM(
                SELECT Pos1, COUNT(Pos1) cnt
                FROM Events
                GROUP BY Pos1
            ) a, (SELECT @rank := 0) b
            ORDER BY a.cnt DESC
          )a
          WHERE a.pos1 = '$pos1'";

$result = mysql_query($query) or die(mysql_error());

$row = mysql_fetch_row($result);

echo $row['Pos1']." ".$row['cnt']." ".$row['rank']."<br />";

```
alternatively do rank within PHP:

```
$query = "SELECT Pos1, COUNT(Pos1) AS qty FROM Events GROUP BY Pos1 ORDER BY qty DESC"; 

$result = mysql_query($query) or die(mysql_error());
$rank=1;
while($row = mysql_fetch_array($result))
{
    echo $rank.": ".$row['Pos1']."(".$row['qty'].")<br />";
    $rank++;
}

```