Help with SQL syntax please

[circuit_breaker]

Hi,

From a table which contains Users (Un), HostNames(Hn) and Files(Fn),
I'd like to know how many HostNames and how many Files belongs to each
user. Each user can have multiple HostNames and Files.

Data Set:

USER HOST FILE
--------------------
U1 H1 F1
U1 H1 F2
U1 H1 F3
U1 H2 F1
U1 H2 F3
U1 H2 F4
U2 H5 F1
U2 H6 F6
U2 H7 F6
U2 H7 F2

Expected Results:

USER HostCnt FilesCnt
---------------------
U1 2 4
U2 3 3

I tried Unions, Joins, imbedded SELECT but it either doesn't work or
it times out.

Thanks for your help.

 

[MGFoster]

Hi,

From a table which contains Users (Un), HostNames(Hn) and Files(Fn),
I'd like to know how many HostNames and how many Files belongs to each
user. Each user can have multiple HostNames and Files.

Data Set:

USER HOST FILE
--------------------
U1 H1 F1
U1 H1 F2
U1 H1 F3
U1 H2 F1
U1 H2 F3
U1 H2 F4
U2 H5 F1
U2 H6 F6
U2 H7 F6
U2 H7 F2

Expected Results:

USER HostCnt FilesCnt

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

If only Access SQL had DISTINCT aggregate functions! But, it doesn't,
so we have to create an obscenity like this:

SELECT user, SUM(IIf(type='host',cnt,0)) As HostCnt,
SUM(IIf(type='files',cnt,0)) As FilesCnt
FROM (
SELECT "host" As Type, user, COUNT(host) As cnt
FROM (SELECT DISTINCT user, host FROM table_name)
GROUP BY user
UNION ALL
SELECT "files", user, COUNT(file)
FROM (SELECT DISTINCT user, file FROM table_name)
GROUP BY user
) As A
GROUP BY user

NOTE: Change "table_name" to your table's name.

If Access had DISTINCT aggregate functions the query would look like
this:

SELECT user, COUNT(DISTINCT host) As HostCnt, COUNT(DISTINCT filename)
As FileCnt
FROM table_name
GROUP BY user

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/AwUBSfkAyYechKqOuFEgEQIUWgCfQ8TlHm9bxja8J0B7/mA2H9hR8oQAoONI
iXtrohuOJ3zpWFmHYBZxcKnE
=2A6k
-----END PGP SIGNATURE-----