Query No. 1
select sum(income) as pairs from Table2 where user_id='100001'
Query No. 2
with cte as
(
select userid, parentid from Table1 where userid = '100001'
union all select t.userid, t.parentid
from Table1 t inner join cte on cte.userid = t.parentid
)
select count(*) from cte
what i want is that join these two queries as a single query in such a way :
subtract query2 retrieved value from query1 retrieved value..
i.e query1_value - query2_value
i am trying like below. but it is giving error..
with cte as
(
select count(*) as pair_count,userid, parentid from user_detail where userid = '100002'
union all select COUNT(*) as pair_count,t.userid, t.parentid
from user_detail t inner join cte on cte.userid = t.parentid
)
SELECT (B.AMOUNT - A.pair_count) AS DUE FROM cte AS A
CROSS APPLY (SELECT SUM(Pairs) AS AMOUNT FROM payout WHERE user_id = '100002') AS B
WHERE A.userid = '100002'
Error is :
Msg 8120, Level 16, State 1, Line 3
Column 'user_detail.userid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 205, Level 16, State 1, Line 1
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.