CODEWITHSUNDEEP
sqlsql-server
Jan
Jan

Reputation: 97

Only select one record if there are more than one of only certain same value of record

enter image description here

In the sql, i wanted all the record but for value RM in column MCCU occurs twice..but i'm in a situation that cannot distinct it because the value of misc of both value are not same..

How can i make if MCCU have more than one RM and then only select the one that have higher position in column POSI and in the misc column, add up their two value together. Hope idea to solve it. Thank you very much!

This is my sql statement

select * from Oclaimc Where cono='NP' and CLNO='7150000032'

Upvotes: 0

Views: 408

Answers (2)

ilhan kaya
ilhan kaya

Reputation: 51

This may help:

 select *
  from oclaimc
 where cono = 'NP'
   and clno = '7150000032'
   and mccu <> 'RM'

 union

 select *
  from (select *
          from oclaimc
         where cono = 'NP'
           and clno = '7150000032'
           and mccu = 'RM'
         order by posi)
 where rownum = 1

Upvotes: 0

Utsav
Utsav

Reputation: 8093

There is no column names misc in your image. I am assuming you need to sum gamntMisc and gttlMisc.

So try this. Add other columns when needed.

select max(CONO) as CONO,max(CLNO) as CLNO,max(posi) as posi,MCCU,
sum(gamntMisc) as totalgamntMisc,sum(gttlMisc) as totalgttlMisc from Oclaimc
where cono='NP' and CLNO='7150000032'
group by mccu

Note: Query will fail if you remove the where clause. If you need this result for each combination of cono and clno then change the group by clause to group by cono,clno,mccu

Upvotes: 1

Related Questions