How to Group By a column without adding another column with aggregate function

Question

I am trying to get a group by for a column "Order_contract_Number" with type as an another column where i am using "Count" aggregator in "case when" function based on "product class" which is another column from the source. Now the issue is that the product class is multiple for same "Order_contract_Number" and the issue araises when i NEED to use "Product class" also in the group by which is causing the issue. Below is the code of what i have written.

SELECT "ORDER_CONTRACT_NUMBER",
   CASE
      WHEN COUNT(distinct "product_class_Sheet1"."PRODUCT CLASS")>1
      THEN 'MIXED TYPE : '||."PRODUCT CLASS" 
      WHEN COUNT(distinct "product_class_Sheet1"."PRODUCT CLASS")=1
      THEN "product_class_Sheet1"."PRODUCT CLASS"||' ONLY '
      ELSE NULL END AS TYPE
  FROM 
  "product_class_Sheet1"
  GROUP BY
    "product_class_Sheet1"."ORDER_CONTRACT_NUMBER"
  , "product_class_Sheet1"."PRODUCT CLASS"

I want the outcome to be

|      ORDER_CONTRACT_NUMBER      |     TYPE             |
|---------------------------------|----------------------|
|          1                      |         MIXED TYPE A |
|          1                      |         MIXED TYPE B | 
|          2                      |         A Only       |
|          3                      |         MIXED TYPE A |
|          3                      |         MIXED TYPE B |
|          3                      |         MIXED TYPE C |
|          3                      |         MIXED TYPE D |
|          3                      |         MIXED TYPE E |

But what i am getting is

|      ORDER_CONTRACT_NUMBER      |     TYPE             |
|---------------------------------|----------------------|
|          1                      |         A ONLY       |
|          1                      |         B ONLY       |
|          2                      |         A ONLY       |
|          3                      |         A ONLY       |
|          3                      |         B ONLY       |
|          3                      |         C ONLY       | 
|          3                      |         D ONLY       |
|          3                      |         E ONLY       |

The Source is :

|      ORDER_CONTRACT_NUMBER      |     PRODUCT CLASS    |
|---------------------------------|----------------------|
|          1                      |         A            |
|          1                      |         B            |
|          2                      |         A            |
|          3                      |         A            |
|          3                      |         B            |
|          3                      |         C            |
|          3                      |         D            |
|          3                      |         E            |

Please let me know how i can tackle this. Thank you

Answer 1

select 
    A."ORDER_CONTRACT_NUMBER", 
    case 
        when count > 1 then 'MIXED TYPE : '||"PRODUCT CLASS" 
        when count = 1 then "PRODUCT CLASS"||' ONLY ' 
        else NULL 
    end as Type 
from source A 
left join 
    (SELECT 
        "ORDER_CONTRACT_NUMBER", 
        count(*) 
     FROM source 
     group by "ORDER_CONTRACT_NUMBER") B 
on 
    A."ORDER_CONTRACT_NUMBER" = B."ORDER_CONTRACT_NUMBER"

Comment: Thank you . This worked perfectly. Can you also show how more than one type for the same order number can be put into the same line, like a concatenation? – Rahul Bhat 1 hour ago

SELECT 
    "ORDER_CONTRACT_NUMBER", 
    case when count(*) > 1 then 'MIXED TYPE : '||LISTAGG(distinct "PRODUCT CLASS")
    else max("PRODUCT CLASS")||' ONLY ' end as type
 FROM source 
 group by "ORDER_CONTRACT_NUMBER"