SQL SELECT issue

[gchq]

Hi there

This seemed the closest to a Sybase forum I can find - and it's probably basic SQL anyway...

Suppose there is a table and the WHERE clause finds 10 records in the row Employee_ID

1
1
1
4
1
1
4
1
1
1

and I want to return the number of employees (in this case 2) how would I do this? It's probably so straightforward I'll kick myself, but at the moment.....

[ck9663]

Hi there

This seemed the closest to a Sybase forum I can find - and it's probably basic SQL anyway...

Suppose there is a table and the WHERE clause finds 10 records in the row Employee_ID

1
1
1
4
1
1
4
1
1
1

and I want to return the number of employees (in this case 2) how would I do this? It's probably so straightforward I'll kick myself, but at the moment.....

try:

select Employee_ID, count(*) from YourTable
group by Employee_ID

[gchq]

try:

select Employee_ID, count(*) from YourTable
group by Employee_ID

The trouble with that is it returns the number of records for each Employee ID! With the example I used that would be:-

Employee_ID - 1 Count(*) 8
Employee_ID - 4 Count(*) 2

Any other ideas?

[ilearneditonline]

try:

select Employee_ID, count(*) from YourTable
group by Employee_ID

try this. It should return a single record of 2

select COUNT(DISTINCT(Employee_ID)) FROM table

[ck9663]

try this. It should return a single record of 2

select COUNT(DISTINCT(Employee_ID)) FROM table

or a query within a query


select count(*) from
(select Employee_ID, count(*) as cnt from YourTable
group by Employee_ID) a

[gchq]

CK9663 and iLearnItOnline - thank you! Distinct was the key that unlocked the door!