Count Unique?

[kermitforney]

Having trouble pulling info out of a database via report.

Need to pull only records that are not equal to 0 and then need to count the records.

So first return the ones that are not "0"

Then count the records not sum the records.

So if there are 4 returned records (4, 15, 6, and 8) then
the answer would be 4. Because I want the number of records not the sum of the values.

I just can't get it right though.

 

[lespaul]

so....this doesn't work?

SELECT Count(*) As CountOfRecords FROM MyTable WHERE SomeField <> 0



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[kermitforney]

This is what I have so far.

SELECT tblCommunity.cmCommunity
FROM tblCommunity INNER JOIN tblPersonal ON tblCommunity.cmID = tblPersonal.cmID
GROUP BY tblCommunity.cmCommunity
HAVING (((tblCommunity.cmCommunity)="Siena Hills") AND ((Count(tblPersonal.plNoChildren))<>0))
ORDER BY Count(tblPersonal.plNoChildren)
WITH OWNERACCESS OPTION;

Problem is it only returns one column (cmCommunity(Siena Hills)). So the result is I only have one co,lumn with one record. "???"

 

[kermitforney]

Any takers?

 

[PHV]

You wanted this ?
SELECT C.cmCommunity, Count(P.plNoChildren) AS yourAlias
FROM tblCommunity AS C INNER JOIN tblPersonal AS P ON C.cmID = P.cmID
GROUP BY C.cmCommunity
HAVING Count(P.plNoChildren) <> 0
ORDER BY 2
WITH OWNERACCESS OPTION;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886