How to COUNT DISTINCT over multiple columns ?

[Thomas Dudziak]

Hi,

with some statement like

  SELECT COUNT(DISTINCT project_id) FROM person_project;

I can get the number of different project_id values (which are of type
INTEGER). But assume that person_project is an indirection table
pointing to tables person and project, and I want to get the number of
distinct pairs (person_id, project_id). Something like

  SELECT COUNT(DISTINCT person_id, project_id) FROM person_project;

would do the trick, but unfortunately this does not work with Derby
("ERROR 42X01: Syntax error: Encountered "," at line 1, column 32.").
The same when using || instead of the comma.
Interestingly, the workaround of

  SELECT COUNT(*) FROM (SELECT DISTINCT person_id, project_id FROM
person_project);

doesn't work either, I get a

  ERROR 42X01: Syntax error: Encountered "<EOF>" at line 1, column 80.

Is there a way to achieve this with Derby ?

regards,
Tom