Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2012
    Posts
    5

    Unanswered: SQL simple help!

    I have this question

    For each year, find the movie that made the largest profit(income - cost) . Display the title year and profit.

    I also have these declarations

    Movie(title: char(25), year: integer, length number(4,2), cost: real, income: real)

    This is my current code but I'm sure it will only get the highest value for all the years, and not every individual year

    SELECT title, year, income-cost AS profit
    FROM movies
    WHERE profit >= ALL (SELECT (income-cost AS profit)
    FROM Movies)

    Where do I go from here to make it get the highest profit for each year and not all of the years combined! Help will be much appreciated!

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Have a look at the MAX function and GROUP BY. This should help resolve your query.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Oct 2012
    Posts
    5
    Quote Originally Posted by it-iss.com View Post
    Have a look at the MAX function and GROUP BY. This should help resolve your query.
    If I use MAX though, wouldn't it only return the highest profit? Group By makes sense but I'm still kinda confused :c

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by neilzepan View Post
    If I use MAX though, wouldn't it only return the highest profit? Group By makes sense but I'm still kinda confused :c
    why not try it and see what happens rather than pontificate on what it might do or not do in the absence of any definitive knowledge?

    If you are confused then there is always da manuel
    http://dev.mysql.com/doc/refman/5.0/...functions.html
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Oct 2012
    Posts
    5
    Wait so will

    SELECT title, year, MAX((income - cost) AS profit)
    FROM emp
    GROUP BY year;

    work?

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I dunno


    here's a wild thought...


    why not try it on your own test data, so you can make a valid assessment of whether it meets your requirements
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Oct 2012
    Posts
    5
    I wish I could but its for an exam and currently dont have the time to go through and make individual test cases

  8. #8
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    You are almost there. Get the MAX(income - cost) per year but you then need to use that in getting entries that match both the year and calculated maximum incomine - cost to display each of these. So you also need subqueries.

    To help you out a little:

    SELECT year, MAX(income-cost) profit
    FROM emp
    GROUP BY year;

    This returned the year and largest profitable movie made for that year. Now you must remember that there may be more than one that have the same level of profitability so you need to get all those that match both the year and the profit.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    fair enough
    if you can't be bothered, then ask yourself why should anyone else be bothered. After all you are getting advice and help form people here free of charge.
    In the intervening 80 minutes since you first posed this question there has in my opinion been more than enough time for you to set up test cases and learn on the job.

    You subtext seems to be that your time is far too important to fritter away on doing, whereas others should volunteer their time because you can be bothered

    You've had a response to your original question, you cannot be bothered to see if it is correct so rather than prove or disprove you want someone else to save you time.. not a nice place to be in.
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Oct 2012
    Posts
    5
    Quote Originally Posted by healdem View Post
    fair enough
    if you can't be bothered, then ask yourself why should anyone else be bothered. After all you are getting advice and help form people here free of charge.
    In the intervening 80 minutes since you first posed this question there has in my opinion been more than enough time for you to set up test cases and learn on the job.

    You subtext seems to be that your time is far too important to fritter away on doing, whereas others should volunteer their time because you can be bothered

    You've had a response to your original question, you cannot be bothered to see if it is correct so rather than prove or disprove you want someone else to save you time.. not a nice place to be in.
    I understand where your coming from, and I apologise for my rudeness

    I finally got it working and thanks to everyone here who helped me! I couldn't have done it without you guys!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •