CODEWITHSUNDEEP
sqloracle-databasegroup-bydistinct
Tyulpan Tyulpan
Tyulpan Tyulpan

Reputation: 764

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?

Upvotes: 0

Views: 94

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

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;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269943

Are you looking for this:

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

Upvotes: 0

Related Questions