work around for Distinct in Count function

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

work around for Distinct in Count function

Author

Topic

Ustele
Starting Member

2 Posts

Posted - 2008-04-11 : 18:01:42

Hi,

I'm trying to count distinct rows for one column and group them by another from the same table, I've seen that Access doesn't support distinct in count function and found the following workaround, but I can't get this to be grouped now by a field called CtyName. What I mean is, how do I get this query to display another column and group it by the same one.

Any ideas on this?

SELECT Count(Pop_Criteria.SciName)
FROM
(SELECT DISTINCT Pop_Criteria.SciName FROM Pop_Criteria);

Any help greatly appreciated

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-04-11 : 19:57:27

something like this:

SELECT CtyName, Count(*)
FROM
(SELECT DISTINCT CtyName, SciName FROM Pop_Criteria) x
group by CtyName

Derived tables can be tricky in access; it is usually easiest to save the inner part as it's own query, and then your main query calls that.

i.e.,

query1:

SELECT DISTINCT CtyName, SciName FROM Pop_Criteria

query2:

select CtyName, count(*) from query1 group by CtyName

- Jeff
http://weblogs.sqlteam.com/JeffS

Ustele
Starting Member

2 Posts

Posted - 2008-04-14 : 16:18:49

Jeff,

Thanks a lot, this seems to work, I was thinking of doing the queries separately, I may have to still, but so far so good.

Cheers,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources