计算每个项目的行数[关闭]

Question

I have below table

ID     Color
 1       red
 2       red
 3       red 
 4       blue
 5       blue
 6       yellow

The result I need :

• red 3
• blue 2
• yellow 1

Answer 1

I also have some kind of database.

mysql> select * from taggings LIMIT 10;
+--------+---------+
| tag_id | post_id |
+--------+---------+
|      2 |      14 |
|      3 |       2 |
|      4 |       1 |
|      4 |       2 |
|      4 |       3 |
|      4 |       4 |
|      4 |       5 |
|      4 |      14 |
|      4 |      19 |
|      6 |       1 |
+--------+---------+
10 rows in set (0.00 sec)

mysql> select post_id, count(tag_id) from taggings GROUP BY(post_id);
+---------+---------------+
| post_id | count(tag_id) |
+---------+---------------+
|       1 |             9 |
|       2 |             3 |
|       3 |             2 |
|       4 |             1 |
|       5 |             3 |
|       6 |             3 |
|       9 |             1 |
|      10 |             3 |
|      11 |             4 |
|      14 |            10 |
|      15 |             4 |
|      16 |             2 |
|      17 |             4 |
|      18 |             5 |
|      19 |             7 |
|      20 |             2 |
+---------+---------------+
16 rows in set (0.00 sec)

php code is straightforward from now on.

<?php 
$cn = mysqli_connect(/*connection details*/);
$sql = "select post_id, count(tag_id) from taggings GROUP BY(post_id);";
$d = mysqli_query($cn, $sql);
echo "post id \t tag count";
echo "<ul>";    
while ($row = mysqli_fetch_assoc($d)) {
echo "<li>";    
echo $row['post_id']."&nbsp &nbsp &nbsp ".$row['count(tag_id)']."<br>"; 
//you will use $row['color'] and $row['count(id)'] 
echo "</li>";   
}
echo "</ul>"?>

output html

post id tag count

1      9
2      3
3      2
4      1
5      3
6      3
9      1
10      3
11      4
14      10
15      4
16      2
17      4
18      5
19      7
20      2