count distinct 3

[Thorsten3]

This query gives me a list of 1085 records:

select distinct clone_id, stage from t_expression

but I don't need the list, I only need to count them
How can I do this?

This command works, but it is not what I want:
select count (distinct clone_id) from t_expression

These commands do not work:
select count (distinct clone_id, stage) from t_expression
select count distinct clone_id, stage from t_expression
select distinct count (clone_id, stage) from t_expression

Does anybody know the proper command??
I'm using DB2

 

[gregsimpson]

As far as I'm aware, the command which you say works is correct. I tried it on DB2 V7.2 and it worked.

select count (distinct clone_id) from t_expression

Why don't you wish to use this?

 

[gregsimpson]

Does the following help?

select count(*) from t_expression
group by clone_id,stage

 

[Thorsten3]

Sorry it was not clear enough.

select count (distinct clone_id) from t_expression

...is not what I'm interested in. I would like to know how many records there are, where the combination of clone_id and stage are distinct. The command above doesn't contain the stage. But if I include the stage:

select count (distinct clone_id, stage) from t_expression

...the command does not work any more, allthough this command:

select distinct clone_id, stage from t_expression

...works well!

 

[gregsimpson]

What about wth the group by example I have given you?

 

[Thorsten3]

Does the following help?
select count(*) from t_expression group by clone_id,stage

basically Yes, but like this:
select distinct clone_id, stage from t_expression

...it gives me a list. As I execute it from a java program, I don't want to run through the loops of the result set and count the loops, but would like to get the result (number of rows) directly with the command.

 

[crufty]

Wrap a query that returns all distinct records of stage and clone_id in a 'select count(*) from ( )' statement:

SELECT count(*)
FROM (
select distinct clone_id, stage
from t_expression
)

This will return a single number that is the number of distinct clone_id, stage rows.

 

[bperry]

I like crufty's suggestion.

SQL Server requires adding an alias name to the pseudo table. Perhaps DB2 is the same... I dunno.


SELECT count(*)
FROM (
select distinct clone_id, stage
from t_expression
) as q

 

[Thorsten3]

Thanks crufty and bperry this worked

SELECT count(*) FROM (
select distinct clone_id, stage
from t_expression) as q