3 Replies - 1665 Views - Last Post: 22 January 2013 - 04:04 PM

#1 James1992  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 130
  • Joined: 30-October 12

How do I fix this SELECT statement?

Posted 22 January 2013 - 11:45 AM

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 linkPosted Image

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


Is This A Good Question/Topic? 0
  • +

Replies To: How do I fix this SELECT statement?

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9497
  • View blog
  • Posts: 35,844
  • Joined: 12-June 08

Re: How do I fix this SELECT statement?

Posted 22 January 2013 - 01:49 PM

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.
Was This Post Helpful? 0
  • +
  • -

#3 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 495
  • View blog
  • Posts: 3,300
  • Joined: 12-January 10

Re: How do I fix this SELECT statement?

Posted 22 January 2013 - 02:09 PM

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.
Was This Post Helpful? 1
  • +
  • -

#4 mojo666  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 356
  • View blog
  • Posts: 783
  • Joined: 27-June 09

Re: How do I fix this SELECT statement?

Posted 22 January 2013 - 04:04 PM

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.
Was This Post Helpful? 2
  • +
  • -

Page 1 of 1