[James1992]

I am trying to write a SQL command, however I am failing badly. I have attached an image of the tables I currently have. Here's the link

Write a SQL command to display for each user who has permissions for files of a total size of more than 50: the user’s id, the total size of all the files the user has permissions for, and the user’s password.

I currently have:

SELECT Permission.userID, User.password, SUM(size) AS TotalSize
FROM Permission, File, User
WHERE Permission.userID = User.id AND
Permission.fileName = File.name AND
SUM(size)>50;

The above code is incorrect. I know that by doing SUM(size) it's just adding up all the file sizes and not grouping them by the usersID. Do I need to add a GROUPBY userID?

If someone could help me I would be extremely grateful />/>

This post has been edited by modi123_1: 22 January 2013 - 01:14 PM

[modi123_1]

Side note - try using normal syntax for your joins..

I would figure you would go from permissions joined against file (on file name)... sum the size in the select query and group by the userid in permission... then take that and join it against the user table to get the password.

[DarenR]

FROM Permission, File, User

not a good idea

you should try to use inner join or outer join depending on the info

like

from permission
inner join user on user.something = permission.something
inner join file on file.something = user.something

how ever i cant view your image to see if i am on the right track or not.

[mojo666]

After altering the joins, you do need to have a GROUP BY statement. Also, an aggregate cannot be part of a WHERE clause, so you will instead have to add a HAVING statement after the GROUP BY to filter total sizes greater than 50.