Menu
I've got a table of 'folders'. I want to return all the records with the userId of 16.
SELECT * FROM `folders` WHERE userId = 16; I've got a table of 'files'. For each 'folder' returned above, I want to return a count of 'files' within that 'folder'.
SELECT COUNT(*) as "Files" FROM files WHERE Folder = n; How do I combine these? I'm lost. Thanks!
569
Conclusion. In this short tutorial, you have seen how the COUNT/GROUP BY/JOIN combination can be used in SQL to aggregate entries across multiple tables. While a GROUP BY query can accomplish this simply enough when working with just one table, the situation can become more complex when working across multiple tables.
If you have a table with members and this table has a column named "group_id", you can just run a query on the members table to get a count of the members grouped by the group_id. This should have the least overhead simply because you are avoiding a join but should still give you what you wanted.
Show activity on this post. $query_string = ' SELECT groups. userGroupID, userGroup, count(users. userGroupID) AS howMany FROM groups_table AS groups JOIN users_table AS users ON users.
SQL Inner Join permits us to use Group by clause along with aggregate functions to group the result set by one or more columns. Group by works conventionally with Inner Join on the final result returned after joining two or more tables.
SELECT fol.* , ( SELECT COUNT(*) FROM files fil WHERE fil.Folder = fol.Folder ) AS "Files" FROM folders fol WHERE fol.userId = 16 It's called a correlated subquery.
http://dev.mysql.com/doc/refman/5.1/en/correlated-subqueries.html
89
you would probably need to use GROUP BY and group it by ID or such:
SELECT folders.*, COUNT(files.*) as filetotal FROM folders LEFT JOIN files ON folders.ID=files.folderID WHERE userId = 16 GROUP BY folders.ID If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
