Results 1 to 14 of 14
  1. #1
    Join Date
    Nov 2008
    Location
    Currently studying in France but not French ;)
    Posts
    11

    Unanswered: How to add 2 columns without using a function

    Hello,
    I'm new to SQL
    I have an employee table with different columns such as name, salary, commission, etc.
    I like to select actual salary which means (salary + commission) where some commissions are NULL.
    The prof. specifically mentioned not using any functions, i.e. COALESC.
    can someone help me on this?

    thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your professor said COALESCE was not allowed??

    what a sadist

    so, you're being graded on inventiveness?

    in that case, it would be wrong of us to hand you an answer, wouldn't it

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2008
    Location
    Currently studying in France but not French ;)
    Posts
    11
    It's just a lab exercise so there is no grading on this.

    You mean you know how to do it and won't tell me?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i know 2 ways to do it, and i will help you think of them, but you have to do the work

    start by imagining a query where you would get to use WHERE commission IS NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2008
    Location
    Currently studying in France but not French ;)
    Posts
    11
    hmmm I'm not sure what you mean but do you mean something like:
    SELECT SUM(salary) AS Actual_Salary FROM emp where tx_commission IS NULL;
    ?
    here I used SUM function but I shouldn't use any functions.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that would give you the total salary of all employees who don't have commission, which isn't what you're after

    SUM() is not allowed???

    what else is not allowed?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2008
    Location
    Currently studying in France but not French ;)
    Posts
    11
    "functions" are not allowed!

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, have you thought about using a CASE expression?

    i'm practically handing you the answer, so you'll have to develop it yourself
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Nov 2008
    Location
    Currently studying in France but not French ;)
    Posts
    11
    actually he doesn't teach that well. He asks the things he hasn't covered in the class. One other question he asked was about the day of our DOB. I had to search on line for it.
    Thanks for the tip, I nee to try this CASE expression. I found it on line as:
    1. CASE column_name
    2. WHEN condition1 THEN result1
    3. WHEN condition2 THEN result2
    4. ...
    5. ELSE result
    6. END
    I need to try it out.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by tennis_girl
    the day of our DOB. I had to search on line for it.
    http://rudy.ca/doomsday.html

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    by the way, what you have there is the simple CASE expression syntax

    i'll give you another hint, you want the searched CASE expression
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Nov 2008
    Location
    Currently studying in France but not French ;)
    Posts
    11
    by the way, actual_salary is not a column of the table.
    The following doesn't work.
    CASE commission
    WHEN commission IS NULL THEN SELECT Salary AS actual_salary FROM emp
    When commission IS NOT NULL THEN SELECT Salary + commission As actual_salary
    END
    a searched CASE? hmm

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you need to check some basic examples for how to write a CASE expression

    for one thing, you cannot just stuff a SELECT clause in there...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Nov 2008
    Location
    Currently studying in France but not French ;)
    Posts
    11
    http://www.postgresql.org/docs/7.4/i...nditional.html
    I did find some examples but they don't do arithmetic expressions like I need to be doing in my case. I know the logic, I just don't know the commands!
    I want it to do something like this
    SELECT commission,
    CASE WHEN commission=NUll THEN Salary=Salary
    CASE WHEN commission= NOT NUll THEN Salary=Salary+ commission

    END
    FROM emp;
    not quite, cause I need to have an "actual_salary" somewhere!
    Hey I just noticed you're a consultant from Toronto, that's cool
    I really like database stuff and I'm seriously thinking to continue on SQL on my Master's
    if you think of it, everything depends on databases.

Posting Permissions

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