CODEWITHSUNDEEP
mysql
Hid Dencum
Hid Dencum

Reputation: 592

MySQL Query Two (COUNT)

I'm experiencing a little trouble with COUNT. I need to count files and folders onto a storage. Here my structure.

DATABASE

STORAGE
  storage_id    storage_name
  1             SERVER 01


FOLDERS
  folder_id   folder_storage_id   folder_name
  1           1                   EXAMPLE FOLDER 

FILES
  file_id    file_folder_id    file_name
  1          1                 test.pdf
  2          1                 test2.pdf
  3          1                 test3.pdf
  4          1                 test4.pdf


QUERY

SELECT  *,

folder_storage_id AS folder_id, COUNT(*) AS folder_qty,

file_folder_id AS file_id, COUNT(*) AS files_qty

FROM
files

LEFT JOIN
folders ON folder_id = file_folder_id

LEFT JOIN
storage ON storage_id = folder_storage_id

I receive same value (4) for $folder_qty and $files_qty

EXPECTED

($folder_qty) 1

($files_qty ) 4

Upvotes: 0

Views: 41

Answers (3)

Gimeniux
Gimeniux

Reputation: 21

You can try this:

SELECT

COUNT(DISTINCT file_folder_id) as folder_qty,
COUNT(file_id) as files_qty

FROM
storage s
LEFT JOIN folders d ON s.storage_id = d.folder_storage_id
LEFT JOIN files f ON d.folder_id = f.file_folder_id

WHERE s.storage_id = 1

Upvotes: 1

Darshan Mehta
Darshan Mehta

Reputation: 30809

To count number of folder ids and file ids for storage, we can use group by clause with count() aggregate function for both folder id and file id; try the following query:

select s.storage_id, count(fo.folder_id), count(fi.file_id)
from storage s left join folders fo on s.storage_id = fo.folder_storage_id
left join files fi on fo.folder_id = fi.file_folder_id
group by s.storage_id;

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

Try this:

SELECT  
   COUNT(DISTINCT folder_storage_id) AS folder_qty,
   COUNT(*) AS files_qty
FROM
   files
LEFT JOIN
   folders ON folder_id = file_folder_id
LEFT JOIN
   storage ON storage_id = folder_storage_id
WHERE 
   storage_id = ?

It doesn't make much sense to select any other columns, since aggregation is performed.

Upvotes: 2

Related Questions