Count distinct

Created a Measure to count the students

Count (
CASE 
WHEN year(ZLDT180_SCR.SCR_DT) = 2002  
AND (dbo.ZLDT180_SCR.ENTYCHLNGSCRVAL_PC >= 0.85  
OR dbo.ZLDT180_SCR.MSTRCHLNGSCRVAL_PC >= 0.85 ) 
Then dbo.ZLDT180_SCR.STDNT_NB
End)

but I do not want the count of duplicate students who have above the criteria and want the count of distict students.

Please help


kprasadreddy (BOB member since 2004-07-02)

/* Formatted on 2004/07/28 19:25 (Formatter Plus v4.8.0) */
CREATE VIEW clever_students
AS
   SELECT DISTINCT dbo.zldt180_scr.stdnt_nb intelligent_student
              FROM dbo.zldt180_scr
             WHERE YEAR (zldt180_scr.scr_dt) = 2002
               AND (   dbo.zldt180_scr.entychlngscrval_pc >= 0.85
                    OR dbo.zldt180_scr.mstrchlngscrval_pc >= 0.85
                   );

So, you’ll have a view CLEVER_STUDENTS with a unique column INTELLIGENT_STUDENT.

The rest is obvious: create a measure count(INTELLIGENT_STUDENT).

Do you agree?


Christian Konrads :it: (BOB member since 2004-07-21)

Is there a way other than creating a view…?Just changing the measure


kprasadreddy (BOB member since 2004-07-02)

why did you post this topic twice? In the Reporter and the designer…


Justin Grimme :us: (BOB member since 2004-03-03)

I posted in Reported and then I thought i should really post in Designer.Can you help?


kprasadreddy (BOB member since 2004-07-02)

Is there a way other than creating a view…?Just changing the measure

You can TRY to create a DIMENSION in Designer called Intelligent Student, with this formula in the select clause:

dbo.zldt180_scr.stdnt_nb

… and this formula in the where field:

 YEAR (zldt180_scr.scr_dt) = 2002
           AND (   dbo.zldt180_scr.entychlngscrval_pc >= 0.85
                OR dbo.zldt180_scr.mstrchlngscrval_pc >= 0.85
               );

At last, create a measure to count Intelligent Student.

But I’m not so sure the duplicates are removed while counting!!


Christian Konrads :it: (BOB member since 2004-07-21)

Can you try (Oracle 9i syntax):

COUNT 
(Distinct
CASE 
   WHEN year (ZLDT180_SCR.SCR_DT) = 2002  
   AND (dbo.ZLDT180_SCR.ENTYCHLNGSCRVAL_PC >= 0.85  
      OR dbo.ZLDT180_SCR.MSTRCHLNGSCRVAL_PC >= 0.85 ) 
   THEN dbo.ZLDT180_SCR.STDNT_NB 
END
)

Also, are you trying to achieve this in Business Objects Designer? If so I will move this topic and delete your other crosspost (please, do not double/cross-post).


Andreas :de: (BOB member since 2002-06-20)

I tried and it works but my other field values are dissapearing as soon as I put a distinct


kprasadreddy (BOB member since 2004-07-02)

The solution is this:

Create a DIMENSION called “STUDENT NOTEBOOK” that has the following sql in the select clause:

dbo.ZLDT180_SCR.STDNT_NB

Now create a MEASURE called “Counter for Student Notebook” that has the following SQL in the select clause:

count(dbo.ZLDT180_SCR.STDNT_NB)

WARNING: The aggregation function must be COUNT and not the default SUM!! <==

So, if you create a new report containing a table with the dimension “STUDENT NOTEBOOK” and the measure “Counter for Student Notebook”, you’ll see the number 1 for each DISTINCT student: if you sum vertically, you obtain the number of distinct students for year 2002.


Christian Konrads :it: (BOB member since 2004-07-21)

Other fields? What other fields?


Christian Konrads :it: (BOB member since 2004-07-21)

This even doen’t work…


kprasadreddy (BOB member since 2004-07-02)

I get partial results and all my fileds in the report are blankwhen I put in distinct…Please advise


kprasadreddy (BOB member since 2004-07-02)

pLEASE LOOK AT sql 2 AND ADVISE…

SQL 1

SELECT
  dbo.ZLDT030_CRS.CRS_NM,
  dbo.ZLDT030_CRS.DRTN_NB,
  dbo.ZLDT030_CRS.EFF_DT,
  dbo.ZLDT800_CRS_DLVRY.CRS_DLVRY_CD_DS,
  dbo.ZLDT030_CRS.CRS_DLVRY_CD,
  dbo.ZLDT075_CRRCLM_CRS.CRS_NB,
  dbo.ZLDT075_CRRCLM_CRS.CRS_VRSN_NB
FROM
  dbo.ZLDT030_CRS,
  dbo.ZLDT800_CRS_DLVRY,
  dbo.ZLDT075_CRRCLM_CRS,
  dbo.ZLDT860_CORE_BUS
WHERE
  ( dbo.ZLDT075_CRRCLM_CRS.CRS_NB=dbo.ZLDT030_CRS.CRS_NB and dbo.ZLDT075_CRRCLM_CRS.CRS_VRSN_NB=dbo.ZLDT030_CRS.CRS_VRSN_NB  )
  AND  ( dbo.ZLDT800_CRS_DLVRY.CRS_DLVRY_CD=dbo.ZLDT030_CRS.CRS_DLVRY_CD  )
  AND  ( dbo.ZLDT860_CORE_BUS.CORE_BUS_AREA_CD=dbo.ZLDT075_CRRCLM_CRS.CORE_BUS_AREA_CD  )
  AND  (
  dbo.ZLDT030_CRS.CRS_STAT_CD   =  1
  AND  dbo.ZLDT860_CORE_BUS.CORE_BUS_AREA_CD  IN  (2, 1)
  )

SQL 2

SELECT
  dbo.ZLDT030_CRS.CRS_NM,
  dbo.ZLDT030_CRS.DRTN_NB,
  dbo.ZLDT030_CRS.EFF_DT,
  dbo.ZLDT800_CRS_DLVRY.CRS_DLVRY_CD_DS,
  dbo.ZLDT030_CRS.CRS_DLVRY_CD,
  Count ( CASE 
WHEN (year(ZLDT180_SCR.SCR_DT) =2002  AND (dbo.ZLDT180_SCR.ENTYCHLNGSCRVAL_PC >= 0.85)  OR  (year(ZLDT180_SCR.SCR_DT) =2002  AND dbo.ZLDT180_SCR.MSTRCHLNGSCRVAL_PC >= 0.85 )) Then dbo.ZLDT180_SCR.STDNT_NB
End)

,
  Count (CASE 

WHEN year(ZLDT180_SCR.SCR_DT) =2003  AND (dbo.ZLDT180_SCR.ENTYCHLNGSCRVAL_PC >= 0.85  OR dbo.ZLDT180_SCR.MSTRCHLNGSCRVAL_PC >= 0.85 )   Then dbo.ZLDT180_SCR.STDNT_NB

End)

,
  Count (CASE 

WHEN year(ZLDT180_SCR.SCR_DT) =2004  AND (dbo.ZLDT180_SCR.ENTYCHLNGSCRVAL_PC >= 0.85  OR dbo.ZLDT180_SCR.MSTRCHLNGSCRVAL_PC >= 0.85 ) Then dbo.ZLDT180_SCR.STDNT_NB

End)

,
  Avg( CASE  When dbo.ZLDT180_SCR.SCR_SQNC_NB =1 AND Year(dbo.ZLDT180_SCR.SCR_DT)=2002  AND dbo.ZLDT180_SCR.QSTN_CATG_CD IN ('EC','MC') THEN dbo.ZLDT180_SCR.ENTYCHLNGSCRVAL_PC                                                END)  ,
  Avg( CASE  When dbo.ZLDT180_SCR.SCR_SQNC_NB =1 AND Year(dbo.ZLDT180_SCR.SCR_DT)=2002  AND dbo.ZLDT180_SCR.QSTN_CATG_CD IN ('EC','MC') THEN dbo.ZLDT180_SCR.MSTRCHLNGSCRVAL_PC                                                END)  ,
  Avg( CASE  When dbo.ZLDT180_SCR.SCR_SQNC_NB =1 AND Year(dbo.ZLDT180_SCR.SCR_DT)=2003  AND dbo.ZLDT180_SCR.QSTN_CATG_CD IN ('EC','MC') THEN dbo.ZLDT180_SCR.MSTRCHLNGSCRVAL_PC                                                END)  ,
  Avg( CASE  When dbo.ZLDT180_SCR.SCR_SQNC_NB =1 AND Year(dbo.ZLDT180_SCR.SCR_DT)=2004  AND dbo.ZLDT180_SCR.QSTN_CATG_CD IN ('EC','MC') THEN dbo.ZLDT180_SCR.MSTRCHLNGSCRVAL_PC                                                END)  ,
  Avg( CASE  When dbo.ZLDT180_SCR.SCR_SQNC_NB =1 AND Year(dbo.ZLDT180_SCR.SCR_DT)=2004  AND dbo.ZLDT180_SCR.QSTN_CATG_CD IN ('EC','MC') THEN dbo.ZLDT180_SCR.ENTYCHLNGSCRVAL_PC                                                END)  ,
  Avg( CASE  When dbo.ZLDT180_SCR.SCR_SQNC_NB =1 AND Year(dbo.ZLDT180_SCR.SCR_DT)=2003  AND dbo.ZLDT180_SCR.QSTN_CATG_CD IN ('EC','MC') THEN dbo.ZLDT180_SCR.ENTYCHLNGSCRVAL_PC                                                END)  
FROM
  dbo.ZLDT030_CRS,
  dbo.ZLDT800_CRS_DLVRY,
  dbo.ZLDT180_SCR
WHERE
  ( dbo.ZLDT030_CRS.CRS_NB=dbo.ZLDT180_SCR.CRS_NB and dbo.ZLDT030_CRS.CRS_VRSN_NB=dbo.ZLDT180_SCR.CRS_VRSN_NB  )
  AND  ( dbo.ZLDT800_CRS_DLVRY.CRS_DLVRY_CD=dbo.ZLDT030_CRS.CRS_DLVRY_CD  )
  AND  (
  dbo.ZLDT030_CRS.CRS_STAT_CD   =  1
  )
GROUP BY
  dbo.ZLDT030_CRS.CRS_NM, 
  dbo.ZLDT030_CRS.DRTN_NB, 
  dbo.ZLDT030_CRS.EFF_DT, 
  dbo.ZLDT800_CRS_DLVRY.CRS_DLVRY_CD_DS, 
  dbo.ZLDT030_CRS.CRS_DLVRY_CD

SQL 3

SELECT
  dbo.ZLDT030_CRS.CRS_NM,
  dbo.ZLDT030_CRS.DRTN_NB,
  dbo.ZLDT030_CRS.EFF_DT,
  dbo.ZLDT800_CRS_DLVRY.CRS_DLVRY_CD_DS,
  dbo.ZLDT030_CRS.CRS_DLVRY_CD,
  count(dbo.ZLDT025_STDNTRSPNS.STDNT_NB),
  dbo.ZLDT025_STDNTRSPNS.ANSW_OPT_CD,
  Count (CASE 

WHEN ZLDT025_STDNTRSPNS.QSTN_SQNC_NB =1  Then dbo.ZLDT025_STDNTRSPNS.STDNT_NB

WHEN ZLDT025_STDNTRSPNS.QSTN_SQNC_NB =2 Then dbo.ZLDT025_STDNTRSPNS.STDNT_NB

WHEN ZLDT025_STDNTRSPNS.QSTN_SQNC_NB =3 Then dbo.ZLDT025_STDNTRSPNS.STDNT_NB

WHEN ZLDT025_STDNTRSPNS.QSTN_SQNC_NB =4 Then dbo.ZLDT025_STDNTRSPNS.STDNT_NB

WHEN ZLDT025_STDNTRSPNS.QSTN_SQNC_NB =5 Then dbo.ZLDT025_STDNTRSPNS.STDNT_NB

End)

,
  Count (CASE 

WHEN ZLDT025_STDNTRSPNS.QSTN_SQNC_NB =6 Then dbo.ZLDT025_STDNTRSPNS.STDNT_NB

WHEN ZLDT025_STDNTRSPNS.QSTN_SQNC_NB =7 Then dbo.ZLDT025_STDNTRSPNS.STDNT_NB

WHEN ZLDT025_STDNTRSPNS.QSTN_SQNC_NB =8 Then dbo.ZLDT025_STDNTRSPNS.STDNT_NB

WHEN ZLDT025_STDNTRSPNS.QSTN_SQNC_NB =9 Then dbo.ZLDT025_STDNTRSPNS.STDNT_NB

WHEN ZLDT025_STDNTRSPNS.QSTN_SQNC_NB =10 Then dbo.ZLDT025_STDNTRSPNS.STDNT_NB

End)

,
  Count ( CASE 

WHEN ZLDT025_STDNTRSPNS.QSTN_SQNC_NB in (1,2,3,4,5) AND ZLDT025_STDNTRSPNS.QSTN_CATG_CD ='EV' AND year(ZLDT025_STDNTRSPNS.RSPNS_DT) =2002  Then dbo.ZLDT025_STDNTRSPNS.STDNT_NB

End)

,
  Count (CASE 

WHEN ZLDT025_STDNTRSPNS.QSTN_SQNC_NB in (1,2,3,4,5)  AND ZLDT025_STDNTRSPNS.QSTN_CATG_CD ='EV' AND year(ZLDT025_STDNTRSPNS.RSPNS_DT) =2003  Then dbo.ZLDT025_STDNTRSPNS.STDNT_NB
End)

,
  Count (CASE 

WHEN ZLDT025_STDNTRSPNS.QSTN_SQNC_NB in (1,2,3,4,5) AND  ZLDT025_STDNTRSPNS.QSTN_CATG_CD ='EV' AND year(ZLDT025_STDNTRSPNS.RSPNS_DT) =2004  Then dbo.ZLDT025_STDNTRSPNS.STDNT_NB


End)

,
  Count (CASE 

WHEN ZLDT025_STDNTRSPNS.QSTN_SQNC_NB in (6,7,8,9,10) AND ZLDT025_STDNTRSPNS.QSTN_CATG_CD ='EV' AND year(ZLDT025_STDNTRSPNS.RSPNS_DT) =2002  Then dbo.ZLDT025_STDNTRSPNS.STDNT_NB

End)
,
  Count (CASE 

WHEN ZLDT025_STDNTRSPNS.QSTN_SQNC_NB in (6,7,8,9,10) AND ZLDT025_STDNTRSPNS.QSTN_CATG_CD ='EV' AND year(ZLDT025_STDNTRSPNS.RSPNS_DT) =2003  Then dbo.ZLDT025_STDNTRSPNS.STDNT_NB

End)
,
  Count (CASE 

WHEN ZLDT025_STDNTRSPNS.QSTN_SQNC_NB in (6,7,8,9,10) AND ZLDT025_STDNTRSPNS.QSTN_CATG_CD ='EV' AND year(ZLDT025_STDNTRSPNS.RSPNS_DT) =2004  Then dbo.ZLDT025_STDNTRSPNS.STDNT_NB


End)

FROM
  dbo.ZLDT030_CRS,
  dbo.ZLDT800_CRS_DLVRY,
  dbo.ZLDT025_STDNTRSPNS
WHERE
  ( dbo.ZLDT030_CRS.CRS_NB=dbo.ZLDT025_STDNTRSPNS.CRS_NB and dbo.ZLDT030_CRS.CRS_VRSN_NB=dbo.ZLDT025_STDNTRSPNS.CRS_VRSN_NB  )
  AND  ( dbo.ZLDT800_CRS_DLVRY.CRS_DLVRY_CD=dbo.ZLDT030_CRS.CRS_DLVRY_CD  )
  AND  (
  dbo.ZLDT030_CRS.CRS_STAT_CD   =  1
  )
GROUP BY
  dbo.ZLDT030_CRS.CRS_NM, 
  dbo.ZLDT030_CRS.DRTN_NB, 
  dbo.ZLDT030_CRS.EFF_DT, 
  dbo.ZLDT800_CRS_DLVRY.CRS_DLVRY_CD_DS, 
  dbo.ZLDT030_CRS.CRS_DLVRY_CD, 
  dbo.ZLDT025_STDNTRSPNS.ANSW_OPT_CD

kprasadreddy (BOB member since 2004-07-02)

Bo gurus Please help me out …


kprasadreddy (BOB member since 2004-07-02)

When executing I get Partial Results in the lower right corner.What does this mean?Please advise…


kprasadreddy (BOB member since 2004-07-02)

Please, refrain from double/cross posting as this is against BOB’s rule #12, see identical post here. Thank you.


Andreas :de: (BOB member since 2002-06-20)

This night I’ll think on how to avoid duplicate count! :wink:

Tomorrow I’ll be here! :wink:

If fact it’s a difficult problem!


Christian Konrads :it: (BOB member since 2004-07-21)

Issue soved…


kprasadreddy (BOB member since 2004-07-02)

kprasadreddy, instead of telling us you solved it, can you show us how you solved it so future similar issues can be resolved?


Justin Grimme :us: (BOB member since 2004-03-03)

Yes, please, explain to us HOW did you solve this difficult problem!!


Christian Konrads :it: (BOB member since 2004-07-21)