Search code examples
sqloracle-databasecountinner-joinaggregate-functions

Counting between two different tables Oracle

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;

Solution

  • 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)