Sql: simultaneous aggregate from two tables
- by Ash
I have two tables: a Files table, which includes the file type, and a File Properties table, which references the file table via a foreign key. Sample Files table:
| id | name | type |
---------------------
| 1 | file1 | zip |
| 2 | file2 | zip |
| 3 | file3 | zip |
| 4 | file4 | jpg |
And the Properties table:
| file_id | property |
-----------------------
| 1 | x |
| 2 | x |
I want to make a query, which shows the count of each file type, and how many files of that type have a property.
So in the example, the result would be
| type | filecount | prop count |
----------------------------------
| zip | 3 | 2 |
| jpg | 1 | 0 |
I could accomplish this by
select f.type, (select count(id) from files where type = f.type), count(fp.id) from
files as f, file_properties as fp where f.id = fp.file_id group by f.type;
But this seems very suboptimal and is very slow. Any better way to do this?
