sqlvertica

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


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


Solution

  • 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"
    

    enter image description here

    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"
    

    enter image description here