Oracle SQL: select count of multiple distinct fields

I need to select id, type and count of rows with unique id-type combinations.

So I tried something like this:

SELECT ID,
       CASE
         WHEN /**/ THEN 'FIRST'
         WHEN /**/ THEN 'ANOTHER'
         ELSE 'ELSE'
         END AS TYPE,
       COUNT(DISTINCT ID, TYPE)

FROM   PRODUCTS

GROUP BY ID, TYPE;

Also the problem is type calculated while processing the query, so when I tried to do something like:

SELECT ID,
       /*SOMETHING*/ END AS TYPE,
       COUNT(*)

FROM (SELECT DISTINCT ID,TYPE FROM PRODUCTS GROUP BY ID,TYPE);

I got this:

 ORA-00904: "TYPE": invalid identifier

Any suggestions?

Answer

You can use a subquery :

SELECT DISTINCT ID,
       (CASE WHEN /**/ THEN 'FIRST'
             WHEN /**/ THEN 'ANOTHER'
             ELSE 'ELSE'
        END) AS TYPE
FROM  PRODUCTS;

Then you can transform it as subquery ::

SELECT ID, TYPE, COUNT(*)
FROM ( <QUERY HERE> ) T
GROUP BY ID, TYPE;

Advertisement

Answer