A group of active records by two separate values

Question

I have a table called Documentsthat looks (partially) as shown below. There is a document identifier, file type and user_id.

id     type  user_id
1      PDF    1 
2      PDF    1 
3      PDF    2 
4      PDF    3 
5      JPG    1 
6      JPG    3 
7      JPG    3 
8      JPG    3 
9      JPG    1
10     PNG    2 
11     PNG    2

What I'm trying to do is count how many users have downloaded each type of file. Therefore, given the data above ... I would like something like:

{"PDF" => 3, "JPG" => 2, "PNG" => 1}

Meaning 3 different users uploaded PDF files, 2 different users uploaded JPG, etc.

I tried to do Document.group(:user_id).group(:type).count, but it gave me a huge hash of each combination user_id/ typewith the score ...

Answer 1

Document.select('distinct user_id').group(:type).count

Answer 2

SQL:

SELECT type, COUNT(DISTINCT user_id) AS cnt 
FROM Documents 
GROUP BY type;

LiveDemo