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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Subtract value 2 from value 1 in sql

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 A
Error is :
[code]Msg 8120, Level 16, State 1, Line 8
Column '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
Go to Top of Page
   

- Advertisement -