COUNT DISTINCT ?

[Jay]

Hi,

I often use aggregate functions in a non-grouped query when I'm writing
queries in T-SQL in an Access .adp server link.

However, when I try the same in a normal .mdb database I receive an
error about a missing operator. The T-SQL is

SELECT COUNT(DISTINCT De_ManTextCode) AS UniqueCodeCount
FROM BIKES.dbo.Der

Can anyone advise how I write the same simple statement in Access SQL
syntax?

Many thanks in advance........Regards, Jason

 

[MGFoster]

Hi,

I often use aggregate functions in a non-grouped query when I'm writing
queries in T-SQL in an Access .adp server link.

However, when I try the same in a normal .mdb database I receive an
error about a missing operator. The T-SQL is

SELECT COUNT(DISTINCT De_ManTextCode) AS UniqueCodeCount
FROM BIKES.dbo.Der

Can anyone advise how I write the same simple statement in Access SQL
syntax?

Many thanks in advance........Regards, Jason

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If that's the complete SQL statement, in Access you'd have:

SELECT De_ManTextCode, Count(*) As UniqueCodeCount
FROM Der
GROUP BY De_ManTextCode

Access doesn't have a Count(Distinct ..) function, lack-a-day.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBR74L8IechKqOuFEgEQLTDwCgoSlPJD9d5u7GBEUtPyqZpvrFFsEAnjLS
oejJ/0ZarKLJNH1bHJJT6W96
=PWU+
-----END PGP SIGNATURE-----

 

[Jay]

Thanks for the help. That seems to give me a list of ALL De_ManTextCode
values along with the count for each. What I want is the overall count
of unique values in this field. So I want just one number returned.
(And to ideally then add a few other fields to the query & get the
countos of those).

Can you advise?......Many thanks, Jason

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Example data from my query:

De_ManTextCode UniqueCodeCount
joe 2
fred 3
suzi 5
isabel 1

I'm guessing you want something like this: There are 11 instances of
names in De_ManTextCode (in my example), but only 4 names; therefore,
you want 4 as the UniqueCodeCount, right? Try this:

SELECT COUNT(*) As UniqueCodeCount
FROM (SELECT De_ManTextCode FROM table_name GROUP BY De_ManTextCode)

The (SELECT...) in the FROM clause is known as a derived table and is a
way to get a subset of some data w/o creating a new query to get it.
Actually, you do create a new query, but it is the derived table.

If you want to add other columns to the output, the whole thing falls
apart. Then ya gotta jump thru hoops to get the d#@$*n thing to work.
I wish Access had Count(Distinct...)! With multiple columns required, I
usually create a VBA function to get the unique count.

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBR7488YechKqOuFEgEQJPhQCfRwlzAE1/MFdBf6nLipExXg1hGtsAnjws
ivH9PyaVyqx+TQWTA4ehuhFE
=VIy9
-----END PGP SIGNATURE-----

 

[Michel Walsh]

You can use a crosstab query. From an initial idea presented by Steve
Dassin:


----------------------------------------------
TRANSFORM COUNT(*) AS value
SELECT myGroup, COUNT(*) AS totalCount, COUNT(value) AS distinctCount
FROM somewhere
GROUP BY myGroup
PIVOT fieldToCount IN( NULL )
---------------------------------------------

Indeed, technically, the crosstab would produce a distinct column for each
distinct fieldToCount and the aggregate COUNT( value ) would count the
number of these columns (those with a not null value). IN(NULL) is a
convenient way to not produce all those columns.



Vanderghast, Access MVP

 

[Jay]

That;s a great help..thanks. I've got a WHERE clause in my derived
tabel subquery and when I try run it I get an 'Invalid bracketing' error
message, but I can't see where? My final SQL is:

SELECT Count(*) AS UniqueCodeCount
FROM (SELECT Mid([De_ManTextCode],2,Len([De_ManTextCode])-2)
FROM tbl_ImportFile
WHERE (((InStr([Profile_Name],"URN"))>"1"))
GROUP BY Mid ([De_ManTextCode],2,Len([De_ManTextCode])-2)) AS
CodeCountSubquery

Any more help greatly appreciated....Thanks, Jay