gistfile1.txt

mysql> create table ulog(userid integer, ltype integer);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into ulog values(1,1), (1,2), (2,1), (2,3), (3,1), (4,1), (4,1), (4,1), (4,2), (4,1), (1,2);
Query OK, 11 rows affected (0.01 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql> select userid, ltype, count(ltype) from ulog group by userid, ltype;
+--------+-------+--------------+
| userid | ltype | count(ltype) |
+--------+-------+--------------+
|      1 |     1 |            1 |
|      1 |     2 |            2 |
|      2 |     1 |            1 |
|      2 |     3 |            1 |
|      3 |     1 |            1 |
|      4 |     1 |            4 |
|      4 |     2 |            1 |
+--------+-------+--------------+
7 rows in set (0.00 sec)