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 ****

 

[Pat Hartman]

Count always counts the number of rows in the recordset or group. It doesn't work the way you think it does.

Select P.Primary_Disability, Count(*), IIf(p.Primary_Disability = 91, "With_Disability", IIf(p.Primary_Disability = 98, "No_Disibality", "Other")) As Category
From YourTable
Group By P.Primary_Disability, IIf(p.Primary_Disability = 91, "With_Disability", IIf(p.Primary_Disability = 98, "No_Disibality", "Other"))

You will end up with as many rows as there are values for Primary_Disability. If only two values are valid, you probably need validation in your data entry forms to prevent any values except 91 and 98.

 

[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...)

 

[Pat Hartman]

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.

I've tried to stay out of this since the thread went back to SQL Server syntax instead of Access syntax.

If you are using Access syntax, rather than hard coding as the other suggested. Take the query I suggested and use it as the source for a crosstab query. That way, it doesn't matter what the values you are pivoting on are, the cross tab will produce as many columns as necessary.