CODEWITHSUNDEEP
sqlsql-serverjoininner-join
shockwave
shockwave

Reputation: 3272

Find subsequent occurrence of a value in a table

I have a table which looks like shown below

ID    SubmittedValue      ApprovedValue
1     25.9                0
1     29                  29
1     25.9                25.9
1     50                  0
1     45                  0
1     10                  0
1     10                  10

Expected result

ID       SubsequentlyApproved(CNT)    Total_Amt_sub_aprvd
1        2                            35.9

We get the above result because 25.9+10 since it is repeated in the subsequent rows.

How to perform VLOOKUP like functionality for this scenario. I tried the subquery but it didn't work.

SELECT a.id,  
SUM(CASE WHEN a.ApprovedValue=0 THEN 1 ELSE 0 END) AS SUB_COUNT
FROM myTable a
join (select id, sum( case when SubmittedValue=ApprovedValue then 1 end) as check_value from myTable) b
on b.id=a.id and SUB_COUNT=check_value

but this is not giving me the expected result.

Upvotes: 0

Views: 59

Answers (2)

Rajat
Rajat

Reputation: 5803

Without window functions using a semi-join

  select id, count(*), sum(submittedvalue)
  from test t1
  where submittedvalue=approvedvalue
  and exists (select 1 
              from test t2
              where t1.id=t2.id and t1.submittedvalue=t2.submittedvalue 
              group by id, submittedvalue 
              having count(*)>1)
  group by id;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269683

You seem to want to count rows where the values are the same and the first value appears more than once. If so, you can use window functions and aggregation:

select id, count(*), sum(ApprovedValue)
from (select t.*, count(*) over (partition by id, SubmittedValue) as cnt
      from t
     ) t
where cnt > 1 and SubmittedValue = ApprovedValue
group by id

Upvotes: 1

Related Questions