How to COUNT DISTINCT on more than one column

oracle, oracle11g, sql

Solution

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"

Problem

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

Original source