Apply value to group-CodePudding

need help with, if any ID with same Groupid has Yes in Payable, add Yes value to Results, otherwise blank. This should be applicable for hundreds of IDs grouped in hundreds of GroupIDs.

ID	GroupID	Payable	Result
111	a	Yes	Yes
222	a		Yes
333	a		Yes
444	b		Yes
555	b	Yes	Yes
777	b		Yes
888	c

I tried to group based on groupID and created a case where groupId count equals or is higher as 1 and the eligibility is Yes.

CodePudding user response：

Your question is a bit unclear due to lack of information about constraints, all the values in table etc. With the given information tho, your task might be done by following query:

with groupData as
(
   Select groupid,payable from put_your_table_name_here
   where payable is not null
   group by groupid
)
Select pt.id
      ,pt.groupId
      ,gd.payable
      ,pt.result
from put_your_table_name_here pt
left join groupData gd on gd.groupid=pt.groupid

The query has it drawbacks- you should give some more info about constraints, but generally it should work. If you wouldnt want to have null values in payable column, you could change left join to join.

CodePudding user response：

WITH CTE(ID,    GroupID,    Payable)AS
(
  SELECT 111,'A','YES'
     UNION ALL
  SELECT 222,'A',''
    UNION ALL
  SELECT 333,'A',''
     UNION ALL
  SELECT 444,'B',''
    UNION ALL
  SELECT 555,'B','YES'
    UNION ALL
  SELECT 777,'B',''
    UNION ALL
  SELECT 888,'C',''


)
SELECT C.ID,C.GroupID,C.Payable,F.FLAG
FROM CTE AS C
JOIN
(
   SELECT X.GROUPID,MAX(PAYABLE)FLAG
     FROM CTE AS X
      GROUP BY X.GroupID
)F ON C.GroupID=F.GroupID
 ORDER BY C.ID;

You can try something like this or

SELECT C.ID,C.GroupID,C.Payable,
 FIRST_VALUE(C.PAYABLE)OVER(PARTITION BY C.GROUPID ORDER BY C.PAYABLE    DESC)XCOL
FROM CTE AS C
ORDER BY C.ID;

CodePudding user response：

with data (ID,GroupID,Payable) as (
Select 111, 'a',    'Yes' from dual union all
Select 222, 'a',    null from dual union all
Select 333, 'a',    null from dual union all
Select 444, 'b',    null from dual union all
Select 555, 'b',    'Yes' from dual union all
Select 777, 'b',    null from dual union all
Select 888, 'c',    null from dual
)
,result as(
select  GroupID, case when Count(*) > 1 then 'Yes' else null end Result
from data
group by  GroupID)
Select * from data
Join result on data.GroupID = result.GroupID
order by data.ID,data.GroupID

Db fiddle link