mysql - 需要帮助创建查询来汇总表中出现的次数

Question

我有一个包含如下数据的表:

event | session id | uid 
---------------------
foo   | 1          | a
bar   | 1          | a
foo   | 2          | b
cat   | 2          | b
cat   | 3          | a
cat   | 4          | c
foo   | 5          | c
cat   | 5          | c
foo   | 6          | b
foo   | 7          | a
dog   | 8          | c
dog   | 9          | b
bat   | 10         | c

我想创建一个查询以返回以下格式的结果:

uid | event | # event used | # of sessions for uid
a   | foo   | 2            | 4 
a   | bar   | 1            | 4
a   | cat   | 1            | 4
b   | foo   | 2            | 4
b   | cat   | 1            | 4
b   | dog   | 1            | 4
c   | cat   | 2            | 5
c   | foo   | 1            | 5
c   | dog   | 1            | 5
c   | bat   | 1            | 5

我尝试使用:

select uid, event, count(*) from events where uid in (select uid from events group by uid) group by uid; 

但这并没有按预期工作。

任何帮助将不胜感激。

Answer 1

select t1.uid, t1.event, count(*) as `# event used`, t2.total_events as `# of sessions for uid`
form table t1
inner join (
   select uid, count(*) as total_events
   from table
   group by uid
) t2 on t2.uid = a.uid
group by t1.uid, t1.event, t2.total_events
order by t1.uid, `# event used` desc