Counting number of groups

[xenya]

Hi,
i need to count the different orders (OrderID) used by employee (E_ID).
Here are some records.
E_ID OrderID
168 472
168 472
168 424
168 180
168 472
168 424
200 372
200 372
200 373
200 373

I used the folowing SQL sentence, but it didn't return what I want.

Select E_ID, count(OrderID)
From Orders
group by E_ID;

And here is what I want.
E_ID Count
168 3 (472,424,180)
200 2 (372,373)

Thanks

[nosepicker]

Here should be a way to do it:

SELECT X.E_ID, COUNT(*)
FROM (Select DISTINCT E_ID, OrderID
From Orders) AS X
GROUP BY X.E_ID

[mikr0s]

Select E_ID, count(DISTINCT OrderID)
From Orders
group by E_ID;

--HTH--

[xenya]

Thanks, both of you.
I used nosepicker's solution, because i also needed to exclude those where count was >1. I did that using "having". It didn't work with mikrOs way.

[xenya]

Thanks, both of you.
I used nosepicker's solution, because i also needed to exclude those where count was >1. I did that using "having". It didn't work with mikrOs way.

[mikr0s]

Select E_ID, COUNT(DISTINCT OrderID)
From Orders
group by E_ID
HAVING COUNT(DISTINCT OrderID) > 1 (when distinct count on order id per E_ID is greater than 1)

Select E_ID, COUNT(DISTINCT OrderID)
From Orders
group by E_ID
HAVING COUNT(*) > 1 (when there is more than one record per E_ID)

--HTH--