CODEWITHSUNDEEP
sqloracle-database
Krushna
Krushna

Reputation: 6050

Oracle applying count function with different conditon on Grouped Data

I have the below SQL query

  SELECT 
  max (bt.NAME) "Batch Type",
  max(st.name) "Submission Status",
  count(sce.CLIP_POINT)--where sce.ACODE is not null
  ,count(sce.CLIP_POINT)--where sce.ACODE is null
  ,sum(sce.CLIP_POINT)--where sce.ACODE is not null
  ,sum(sce.CLIP_POINT)--where sce.ACODE is null
  from SUBMISSION_DATA_ENTRY sde,
  BATCH_PROCESSOR bp, status st,SUBMISSION_CLIP_ENTRY sce,
  SUBMISSION_PERIOD sp,BATCH_TYPE bt
  where sde.sub_perd_id=sp.sub_perd_id 
  and sde.bpr_pk=bp.bpr_pk and sde.sts_pk=st.sts_pk
  and sce.sde_pk=sde.sde_pk 
  and bt.bat_pk =bp.bat_pk
  group by bt.bat_pk, st.sts_pk;

Here I need to apply two count one for sce.ACODE is not null and one for other where sce.ACODE is null. How I can count the same column with different condition. Please help

Edit

How to sum the same column with different condition

Upvotes: 0

Views: 70

Answers (2)

Joe
Joe

Reputation: 6827

In SELECT:

To count nulls:

count (case when sce.acode is null then 1 else null end)

To count non-nulls:

count(sce.acode)

EDIT Based on comment below, requirement is to sum clip_point, so:

-- sum of clip_point when acode is null
sum(case when sce.acode is null then sce.clip_point else 0 end)

 -- sum of clip_point when acode is not null 
sum(case when sce.acode is null then 0 else sce.clip_point end)

Upvotes: 2

jle
jle

Reputation: 9489

How about using a sum?:

SELECT 
  max (bt.NAME) "Batch Type",
  max(st.name) "Submission Status",
  sum(case when sce.CLIP_POINT is not null then 1 else 0 end)--where sce.ACODE is not null
  ,sum(case when sce.CLIP_POINT is null then 1 else 0 end)--where sce.ACODE is null
  from SUBMISSION_DATA_ENTRY sde,
  BATCH_PROCESSOR bp, status st,SUBMISSION_CLIP_ENTRY sce,
  SUBMISSION_PERIOD sp,BATCH_TYPE bt
  where sde.sub_perd_id=sp.sub_perd_id 
  and sde.bpr_pk=bp.bpr_pk and sde.sts_pk=st.sts_pk
  and sce.sde_pk=sde.sde_pk 
  and bt.bat_pk =bp.bat_pk
  group by bt.bat_pk, st.sts_pk;

Upvotes: 1

Related Questions