#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2016
    Posts
    81
    Rep Power
    2

    Trying to subtract


    Hi

    I am trying to get a result of the remaining picks. I am not sure how to put these together to do the math?

    The first snippet gives the total number of vacation weeks for all employee. The second snippet gives the used vacation picks of all employees
    All I am interested in is how many total picks remain.

    Thanks for looking

    Code:
    SELECT SUM(e.vac_weeks) as Total
    FROM employee as e 
    WHERE e.platoon_id = 1
    Code:
    SELECT SUM(v.week_cost)
    FROM vacation_picks as v
    WHERE platoon_id = 1 and YEAR(v._date1) = 2018
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,793
    Rep Power
    4331
    Code:
    SELECT Total
         , Taken
         , Total - Taken AS Remaining
      FROM ( SELECT ( SELECT SUM(e.vac_weeks) 
                        FROM employee
                       WHERE platoon_id = 1 ) AS Total
                  , ( SELECT SUM(v.week_cost)
                        FROM vacation_picks
                       WHERE platoon_id = 1 
                         AND YEAR(_date1) = 2018 ) AS Taken
           ) AS foo

    Comments on this post

    • SGC3 agrees : Thanks!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2016
    Posts
    81
    Rep Power
    2
    Thank you as always

IMN logo majestic logo threadwatch logo seochat tools logo