How to COUNT DISTINCT on more than one column

Question

I have the following table.

group _id   p_id    version value
1              1       1    10
1              1       2    11
1              1       2    12
1              2       3    13
2              1       2    14
2              1       3    15
2              1       2    16

I would like to count on how many records for each group_id and how many distinct p_id + version for each group_id. I have following query

SELECT "group_id",count(*) , count(distinct "p_id","version")
FROM tbl
group by "group_id"

Aapparently, it' not going to work, as Oracle will give me error on COUNT

ORA-00909: invalid number of arguments 

I know this can be done by subquery. However, is there any simple way to get same result? Considing the performance is important to me, as we have more than 500 million records in the table.

SQL Fiddle

Answer 1

I don't know if it's the best way, but I normally concatenate the two values, using a delimiter to enforce "distinctness", so they become one expression, which Oracle can handle with COUNT DISTINCT:

SELECT "group_id",count(*) , count(distinct "p_id" || '-' || "version")
FROM tbl
group by "group_id"