Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
randhawa_125
Starting Member
2 Posts |
 Posted - 2014-02-23 : 10:54:20
|
| [code]with cte as( select userid, parentid from user_detail where userid = '100002' union all select t.userid, t.parentid from user_detail t inner join cte on cte.userid = t.parentid ) SELECT (10 - B.AMOUNT) 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'[/code] i have to change below line from above code :SELECT (10 - B.AMOUNT) AS DUE FROM cte AS A i have to subtract, total number of rows in cte from B.Amount instead of 10 - B.Amount. i.e No.of rows in cte - B.Amount. when i try something like below it gives error :SELECT (count(*) - B.AMOUNT) AS DUE FROM cte AS AError is :[code]Msg 8120, Level 16, State 1, Line 8Column 'B.AMOUNT' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.[/code] otherwise it works fine..Hope you understand my question.. |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2014-02-24 : 00:12:52
|
| SELECT ( (select count(*) from cte) - B.AMOUNT) AS DUE FROM cte AS A |
 |
|
|
|
|
|
Subtract value 2 from value 1 in sql