MySQL 查询计数基于不同列值的用户和文件名

Question

我正在尝试编写一个查询，其中我计算每个用户查看文件的次数和下载次数。

Dataset
Userid | callaction | description
1      | Viewed     | abc.pdf
2      | Viewed     | abc.pdf
2      | Viewed     | xyz.pdf
1      | Viewed     | abc.pdf
1      | Downloaded | abc.pdf
1      | Downloaded | abc.pdf
1      | Downloaded | abc.pdf
2      | Downloaded | xyz.pdf
1      | Downloaded | xyz.pdf

我的查询是：

select userid, description, 
count(description) as 'Number of views',
count(description) as 'Number of Downloads'
from tablename
where callaction = 'VIEWFILE' OR callaction = 'DOWNLOAD'
group by userid, description;

但这给了我一个表格，其中查看和下载是相同的，这是错误的

我正在寻找的结果是：

Userid | callaction | description | Number of views | Number of Downloads
1      | Viewed     | abc.pdf     | 2               |  3
1      | Viewed     | xyz.pdf     | 0               |  1
2      | Viewed     | abc.pdf     | 1               |  0
2      | Viewed     | xyz.pdf     | 1               |  1

Answer 1

select userid, description, 
sum(callaction = 'VIEWFILE') as 'Number of views',
sum(callaction = 'DOWNLOAD') as 'Number of Downloads'
from tablename
group by userid, description;