Subtract value 2 from value 1 in sql

Question

Subtract value 2 from value 1 in sql

randhawa_125

Valued Member

Points: 70
More actions
February 23, 2014 at 9:04 am

#304759

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'
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 :
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.
otherwise it works fine..
Hope you understand my question..

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

gstarsaini SSC Eights! Points: 980 More actions · Answer 1

I have one douby,while defining cte you have used cte to join within the with clause,if that is correct then you can try this:

Here you can create row_number column in cte and then use it in the SELECT statement.What I am trying to say is :

" SELECT (Max(cte.RN) - B.AMOUNT) AS DUE

FROM (SELECT *,Row_number () over (order by userid) as 'RN'

FROM (

select userid, parentid

from user_detail where userid = '100002'

union all

select t.userid, t.parentid

from user_detail t

inner join T

on T.userid = t.parentid

)T )cte

CROSS APPLY (SELECT SUM(Pairs) AS AMOUNT FROM payout WHERE user_id = '100002') AS B

WHERE cte.userid = '100002' "

Divine Flame SSCoach Points: 15943 More actions · Answer 2

You may need to put it like this:

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 ((SELECT COUNT(*) FROM cte) - 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'

Sujeet Singh

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

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

d.userid,

[DUE] = (d.[Rows] - b.AMOUNT)

FROM (

SELECT userid, [Rows] = COUNT(*)

FROM cte

WHERE userid = '100002'

) d

CROSS APPLY (

SELECT [AMOUNT] = SUM(p.Pairs)

FROM payout p

WHERE p.user_id = d.userid

) b

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden