SQL & PL/SQL

Hi all,

I am trying to do a single query for the following situation, please let me know if this is possible, maybe using analythics.

My table:

PK_ID SAMPLE_ID HABITAT_TYPE EFFORT_NO SPECIES
----------------------------------------------------------------------------------------------------------
1 1 Riffle 1 A
2 1 Riffle 1 B
3 1 Riffle 1 C
4 1 Riffle 2 B
5 1 Riffle 2 D
6 1 Pool 3 C
7 1 Pool 3 D
8 1 Pool 3 E
9 1 Riffle 4 F
10 2 Pool 1 A

I want a query that groups the data by SAMPLE_ID, HABITAT_TYPE and EFFORT_NO, ordered by PK_ID. In the result, I need to count the DISTINCT SPECIES for the current EFFORT_NO and ALL the previous EFFORT_NOs. The result should look like this:


SAMPLE_ID HABITAT_TYPE EFFORT_NO COUNT
--------------------------------------------------------------------------------------------------
1 Riffle 1 3
1 Riffle 2 4
1 Pool 3 5
1 Riffle 4 6
2 Pool 1 1

So, for the first result row, A, B, and C should be counted as 3 from PK_IDs (1,2,3).

For the second result row, A, B, C and D should be counted as 4 from PK_IDs (1,2,3,4,5)

For the third result row, A, B, C, D and E should be counted as 5 from PK_IDs (1,2,3,4,5,6,7,8)

Can this be done with one sigle query ?

Thanks,
Danny