Results 1 to 11 of 11
  1. #1
    Join Date
    May 2008
    Posts
    6

    Unhappy Unanswered: Please Help: Sql & Sum

    Hi

    Is anyone able to help me?

    This is what i have:

    Plane_Type PROFIT
    Boeing 717 4275
    Boeing 717 4275
    Boeing 747 5595.5
    Boeing 747 5595.5
    Boeing 747 5595.5
    Boeing 720 4322.5

    SELECT Planes.Plane_Type, Planes.Num_Seats*9.5 AS PROFIT
    FROM Planes
    WHERE [Num_Seats]*9.5 AND [Num_Seats]>100;

    But i don't want the duplicates

    - so i added DISTINCT but then i also need the SUM of the rows which are the same ie: Boeing 717 should equal 4275+4275..

    Please can anyone help me i just can't get it to work i was also look at the group by but i just can't get it working.

    hope someone can help ASAP/

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    WHERE [Num_Seats]*9.5 AND [Num_Seats]>100;
    This is invalid (ok, invalid is a strong word, it does not using the logic you expect).
    Perhaps you meant
    Code:
    WHERE [Num_Seats]*9.5>100 AND [Num_Seats]>100;
    Code:
    SELECT plane_type
         , Sum(num_seats * 9.5) As [profit]
    FROM   planes
    GROUP
        BY plane_type
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    hint: GROUP BY

    it's in every basic SQL tutorial

    is this an SQL assignment, or are you free click on the cute little Access toolbar buttons that accomplish a GROUP BY through the friendly graphical user interface?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    May 2008
    Posts
    6
    Thanks for replying so fast

    i think i forgot to tell you that yes this is a sql assignment and

    the >100 is becasue i only want it to show plane type that have more than 100 seats.

    THANKS FOR YOUR HELP
    i am using

    SELECT plane_type , Sum(num_seats * 9.5) As [profit]
    FROM planes
    WHERE [Num_Seats]>100
    GROUP
    BY plane_type


    only 5 questions to go now before by deadline
    thanks again i have been trying this for hours!

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Damnit, caught out. You've been added to the naughty list choco.holic and I shant be answering your questions so thoroughly in a hurry.

    Answer me this; do you understand the solution provided?
    George
    Home | Blog

  6. #6
    Join Date
    May 2008
    Posts
    6
    Basically same question but this time i am trying to
    only show the result of the num_seats * 9.5 which equals at least 1500 in the profit then add those profits - if that makes sense.

    SO taking my previous one:

    SELECT plane_type, Sum(num_seats * 9.5) AS profit
    FROM planes
    WHERE [Num_Seats]>100
    GROUP BY plane_type;

    I no longer need >100
    but need something like
    WHERE [Num_Seats]*9.5 is >=1500

    SELECT Plane_Type, Num_Seats
    FROM Planes
    WHERE [Num_Seats]*9.5>1500;

    any suggestions?

  7. #7
    Join Date
    May 2008
    Posts
    6
    Yes I Understand

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You didn't answer my question above; and until you do I will not help
    George
    Home | Blog

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Oooh, sniped...

    Ok, can you EXPLAIN it then please?
    George
    Home | Blog

  10. #10
    Join Date
    May 2008
    Posts
    6
    i slightly modified that solution to come up with exactly what i needed
    you can see i was on right track

    i am a distance student so all the help i can get is great, and i am learning from it

    if i was so bad i would have posted all 15 questions

  11. #11
    Join Date
    May 2008
    Posts
    6
    no cause i have less then 20 minutes to finish this assignemnt and i do not have time

    thanks for your previous help though

Posting Permissions

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