CODEWITHSUNDEEP
mysqlsql
J Young
J Young

Reputation: 755

Sum and count number of users within the same table

Given the following table structure, I want to sum the number of users who have uploaded just 1 file, 2 files, 3 files all the way up to n files.

I'm struggling to wrap my head around this query - any pointers?

TABLE

+----+--------+-----------+---------------------+
| Id | UserId | FileName  | CreatedAt           |
+----+--------+-----------+---------------------+
| 1  | 4      | test.jpg  | 2019-09-05 14:42:12 |
| 2  | 4      | truth.png | 2019-09-05 14:42:12 |
| 3  | 5      | gen.jpg   | 2019-09-05 14:42:12 |
| 4  | 6      | bob.jpg   | 2019-09-05 14:42:12 |
+----+--------+-----------+---------------------+

DESIRED OUTCOME

+-----------------+------------------+
| Number of Items | Count            |
+-----------------+------------------+
| 1 uploaded      | 2 users          |
| 2 uploaded      | 1 users          |
+-----------------+------------------+

Upvotes: 0

Views: 43

Answers (2)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

Firstly, get the number of files uploaded by a specific user in a subquery, using GROUP BY with COUNT(*) aggregation. Afterwards, you can use this result-set as a Derived Table, to count the number of users which has same number of uploads (group by on the previously calculated count):

SELECT 
  dt.total_files_uploaded, 
  COUNT(dt.UserId) AS num_of_users
FROM 
( 
  SELECT UserId, COUNT(*) AS total_files_uploaded
  FROM your_table 
  GROUP BY UserId
) AS dt
GROUP BY dt.total_Files_uploaded

Upvotes: 4

elliottmjohnson
elliottmjohnson

Reputation: 19

SELECT ab.UploadedFiles, COUNT(ab.UserId) AS NumUsers
( SELECT UserId, COUNT(*) AS UploadedFiles 
  FROM table_name 
  GROUP BY UserId
) AS ab

Cheers

Upvotes: 0

Related Questions