Counting between two different tables Oracle

Question

I have two ORACLE tables, FOLDER and FILES. Each folder contains several files. I am trying to get the number of files for number of folders. The number of folders x that contains the number of files y. For example 50 folders contain 10 files, 35 folders contain 8 files... Can I get some help please on the query :

select count(fl.id_folder) ,count(fi.fileID) from FOLDER fl inner join FILES fi on fl.id_folder=fi.fileID group by fl.id_folder;

Answer 1

You can use two levels of aggregation. Assuming that table files has a column called id_folder, you would do:

select cnt_files, count(*) cnt_folders
from (
    select count(*) cnt_files
    from files 
    group by id_folder
) t
group by cnt_files

Answer 2

We can write the query using group by as follows:

Select cnt_files, count(1) as num_of_folders
from
(select fl.id_folder, count(fi.fileid) as cnt_files
  from FOLDER fl 
  Left join FILES fi on fl.id_folder=fi.fileID
Group by fl.id_folder)
Group by cnt_files;

Note: I have used the LEFT JOIN to consider all the folders (With and Without files in it)