Count distinct

[Tr4c3yy]

HI
I am baffled and have tries all ways to count Distinct information.

My database records learners and enrolments, and I need to count the unique number of learners for different categories, such as gender and Disability but SQL results are counting the number of enrolments.
In this SQL the Learner count is correct but the Disability total add up to the amount of enrolments.

SELECT
count(distinct p.PERSON_CODE) Learners

,count(Case when p.Primary_Disability=91 then 1 End) 'With_Disability'
,count(Case when p.Primary_Disability=98 then 1 End) 'No_Disability'
,count(Case when p.Primary_Disability is Null then 1 End) 'Blank'

From ****

 

[Tr4c3yy]

Thank you
!

I tweaked yours a little and my totals are correct.
SELECT
count(distinct p.PERSON_CODE) Learners
,p.Sex Gender
,IIf(p.Primary_Disability = 91, 'With_Disability', IIf(p.Primary_Disability = 98,'No_Disibality', 'Other')) As Category

Perfect answers

I am just wondering if there is a way to have the categories as the titles of the columns and the Gender as the Row titles? This would enable me to simply screen shot these figures across whenever asked. I am often asked for figures for all different categories.

 

[Minty]

This is air code untested but something like

SELECT
        COUNT(distinct p.PERSON_CODE) as Learners,
        SUM(CASE WHEN p.Primary_Disability = 91 THEN 1 Else 0 END) As 'With Diability',
        SUM(CASE WHEN p.Primary_Disability = 98 THEN 1 Else 0 END) As 'No Diability',
        SUM(CASE WHEN p.Primary_Disability is Null THEN 1 Else 0 END) As 'Other'
        FROM  YourTable p

 

[Tr4c3yy]

This is air code untested but something like

SELECT
        COUNT(distinct p.PERSON_CODE) as Learners,
        SUM(CASE WHEN p.Primary_Disability = 91 THEN 1 Else 0 END) As 'With Diability',
        SUM(CASE WHEN p.Primary_Disability = 98 THEN 1 Else 0 END) As 'No Diability',
        SUM(CASE WHEN p.Primary_Disability is Null THEN 1 Else 0 END) As 'Other'
        FROM  YourTable p

Thank you for helping.

when I use this code answers count the number of enrolments, not the number of unique learners.

 

[MarkK]

I don't know SQL Server well, but in Jet I might do...

SELECT
        Count(p.*) as Learners,
        -SUM(p.Primary_Disability = 91) As 'With',
        -SUM(p.Primary_Disability = 98) As 'Without',
        -SUM(p.Primary_Disability is Null) As 'Other'
        FROM  YourTable As p

....because a boolean expression is itself a number, and you can just sum the result of the expression directly.

 

[Tr4c3yy]

I don't know SQL Server well, but in Jet I might do...

SELECT
        Count(p.*) as Learners,
        -SUM(p.Primary_Disability = 91) As 'With',
        -SUM(p.Primary_Disability = 98) As 'Without',
        -SUM(p.Primary_Disability is Null) As 'Other'
        FROM  YourTable As p

....because a boolean expression is itself a number, and you can just sum the result of the expression directly.

HI I tried this but I have a Incorrect syntax near ')' on the first sum line which I cannot figure out

 

[Isaac]

HI
I am baffled and have tries all ways to count Distinct information.

My database records learners and enrolments, and I need to count the unique number of learners for different categories, such as gender and Disability but SQL results are counting the number of enrolments.
In this SQL the Learner count is correct but the Disability total add up to the amount of enrolments.

SELECT
count(distinct p.PERSON_CODE) Learners

,count(Case when p.Primary_Disability=91 then 1 End) 'With_Disability'
,count(Case when p.Primary_Disability=98 then 1 End) 'No_Disability'
,count(Case when p.Primary_Disability is Null then 1 End) 'Blank'

From ****

instead, do something like counting the distinct DisabilityKey, if one exists.

remember, you can also do stuff like: "count(distinct case...)