Hi,

there are three tables:
1) department
---------------
ID (primary key)
name

2) reports
----------
ID (p. key)
depID
userID

3) users
--------
ID (p.key)
name

I want to get in one query how many distinct users have made a report for
each department.
E.g.: table reports may look like this:
ID depID userID
1 1 1
2 1 1
3 1 3
4 3 6
5 4 8

This gives:
for dep 1: 2 distinct users
for dep 2: 0
for dep 3: 1 distinct user
for dep 4: 1 distinct user

Thanks for
Chris

On Mon, 1 Oct 2007 16:58:36 +0200, Chris wrote:

>Hi,
>
>there are three tables:
>1) department
>---------------
>ID (primary key)
>name
>
>2) reports
>----------
>ID (p. key)
>depID
>userID
>
>3) users
>--------
>ID (p.key)
>name
>
>I want to get in one query how many distinct users have made a report for
>each department.
>E.g.: table reports may look like this:
>ID depID userID
>1 1 1
>2 1 1
>3 1 3
>4 3 6
>5 4 8
>
>This gives:
>for dep 1: 2 distinct users
>for dep 2: 0
>for dep 3: 1 distinct user
>for dep 4: 1 distinct user
>
>Thanks for
>Chris
>

Hi Chris,

School assignment?

SELECT d.ID, COUNT(DISTINCT r.userID)
FROM department AS d
LEFT OUTER JOIN reports AS r
ON r.depID = d.ID;

(Untested - see www.aspfaq.com/5006 if you prefer a tested reply)

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

thanks (it's a boss assignement)

"Hugo Kornelis" <hugo@perFact.REMOVETHIS.info.INVALID> schreef in bericht
news:5762g3d6sgb9fsu4t5pmkk5ua8b33ib73i@4ax.com...
> On Mon, 1 Oct 2007 16:58:36 +0200, Chris wrote:
>
>>Hi,
>>
>>there are three tables:
>>1) department
>>---------------
>>ID (primary key)
>>name
>>
>>2) reports
>>----------
>>ID (p. key)
>>depID
>>userID
>>
>>3) users
>>--------
>>ID (p.key)
>>name
>>
>>I want to get in one query how many distinct users have made a report for
>>each department.
>>E.g.: table reports may look like this:
>>ID depID userID
>>1 1 1
>>2 1 1
>>3 1 3
>>4 3 6
>>5 4 8
>>
>>This gives:
>>for dep 1: 2 distinct users
>>for dep 2: 0
>>for dep 3: 1 distinct user
>>for dep 4: 1 distinct user
>>
>>Thanks for
>>Chris
>>
>
> Hi Chris,
>
> School assignment?
>
> SELECT d.ID, COUNT(DISTINCT r.userID)
> FROM department AS d
> LEFT OUTER JOIN reports AS r
> ON r.depID = d.ID;
>
> (Untested - see www.aspfaq.com/5006 if you prefer a tested reply)
>
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis