CODEWITHSUNDEEP
sqlsql-serverdatabaset-sqlsubtraction
victor13
victor13

Reputation: 39

Need to subtract two columns using SQL

I have this query which gives me the number of license used and total number of licenses, but I need to subtract these columns and name result column as "number available".

What I need is (Total number - number of license users = number available)

Query so far:

SELECT Count(u.user_id) "Number Used",
       kp.temp_num_license
FROM   products p,
       user_products u
WHERE  user_id IN (SELECT user_id
                   FROM   t_users
                   WHERE  end_date IS NULL)
       AND kp.product_id IN ( 2, 3, 4, 5, 7, 8 )
       AND u.product_id = p.product_id
GROUP  BY temp_num_license

I would really appreciate any help on this one.

Upvotes: 2

Views: 3689

Answers (1)

Darren
Darren

Reputation: 70718

SELECT Count(u.user_id) "Number Used", kp.temp_num_license, COUNT(u.user_id) - (kp.temp_num_licencse) "Number Available"
FROM products p,user_products u 
WHERE user_id in (select user_id from t_users where end_date is null) and kp.product_id in (2,3,4,5,7,8) AND u.product_id=p.product_id group by temp_num_license

Upvotes: 2

Related Questions