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"

Answer 2

Oracle:

select order_contract_number, 
    CASE
        WHEN count(*)>1 THEN 'MIXED TYPE : '|| listagg(productclass, ', ')
        WHEN count(*)=1 THEN listagg(productclass, ', ') ||' ONLY '
    ELSE NULL END AS TYPE
from source
group by order_contract_number;

source:

output:

Answer 3

Pivot the input into dedicated columns in a GROUP BY query, then join the base input with that pivot, check if both pivoted values are NOT NULL to decide whether you use 'MIXED' or 'ONLY' .

I added two additional rows to your input to demonstrate the behaviour in all three possible cases.

It would also work if you had several equal combinations of order_contract_number and product_class.

WITH 
-- your input ...
indata(order_contract_number,product_class) AS (
          SELECT 1,'A'
UNION ALL SELECT 1,'B'
UNION ALL SELECT 2,'A'
UNION ALL SELECT 3,'B'
)
-- real query starts here, replace following comma with "WITH"
,
analysed AS (
  SELECT
    order_contract_number
  , MAX(CASE product_class WHEN 'A' THEN 1 END) AS a_count
  , MAX(CASE product_class WHEN 'B' THEN 1 END) AS b_count
  FROM indata
  GROUP BY 1
)
SELECT
  i.order_contract_number
, CASE
    WHEN a.a_count IS NOT NULL AND a.b_count IS NOT NULL
    THEN 'MIXED TYPE '
    ELSE 'ONLY  TYPE '
  END ||product_class AS type
FROM indata   AS i
JOIN analysed AS a USING(order_contract_number)
 order_contract_number |     type     
-----------------------+--------------
                     1 | MIXED TYPE A
                     1 | MIXED TYPE B
                     2 | ONLY  TYPE A
                     3 | ONLY  TYPE B