Click here to Skip to main content
15,886,791 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Query No. 1
SQL
select sum(income) as pairs from Table2 where user_id='100001'

Query No. 2
SQL
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..
SQL
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 :
SQL
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.
Posted
Updated 22-Feb-14 23:24pm
v2

1 solution

Answer supplied at the reposted question Subtract value 2 from value 1 in sql[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900