Substracting two different columns from 2 selections on same table

Question

I have these two queries as below :

SELECT globalid, name, price, sum(qnt) as pozitive 
from main 
where [date-out] is null 
group by globalid, name, price;

this query gives the sum of quantity of different items in two types of dates, date-created and date-in.

SELECT globalid,  sum(qnt) as negative
from main 
where [date-out] is not null 
group by globalid;

this query gives the sum of quantity taken out of storage of different items in date-out -s.

I want to make a DataSet that has the following fields :

globalid - name - price - in stock - sold - total

I have found some examples online but, they are mainly with the count function, or if with sum, only one of the queries has a condition, not both. I am using SQL Server, any help is appreciated.

Answer 1

Seems like you can use CASE with SUM -- no need for any subqueries:

SELECT 
    globalid, 
    name,
    price,
    sum(case when [date-out] is null then qnt end) positive,
    sum(case when [date-out] is not null then qnt end) negative,
    sum(qnt) total
from main
group by
    globalid, 
    name,
    price

Answer 2

select x.globalid, x.name, x.price, x.positive as [in stock], x.negative as [sold], x.positive + x.negative as [total]
from
(
SELECT globalid,  
           name, 
          price,
   sum(case when [date-out] is not null then qnt else 0 end) as negative,
   sum(case when [date-out] is null then qnt else 0 end) as positive 
from main 
where [date-out] is not null 
group by globalid, name, price
) as x