CODEWITHSUNDEEP
sqlsql-serveraggregate
artemis
artemis

Reputation: 7261

Aggregating a row if it appears multiple times

I have a set of data that looks like this:

| Upper_Level_Part_Number | CUM_PART_NUM | Alternate_Part | Make_Buy | Quantity_Needed |
|:-----------------------:|:------------:|:--------------:|:--------:|:---------------:|
|          AAB233         |    IEO333    |        N       |     B    |        1        |
|          AAB233         |    IEO333    |        N       |     M    |        1        |
|          AAB233         |    IEO333    |        Y       |     B    |        1        |
|          AAB233         |    IEO333    |        Y       |     M    |        1        |

What I am trying to do is create a selection of this data that looks like this:

| Upper_Level_Part_Number | CUM_PART_NUM | Alternate_Part | Make_Buy | Quantity_Needed |
|:-----------------------:|:------------:|:--------------:|:--------:|:---------------:|
|          AAB233         |    IEO332    |        Both    |     Both |        1        |

Essentially mimicking the logic that says:

If a CUM_PART_NUM has both a B and an M for Make_Buy, change it to Both. If a CUM_PART_NUM has an N and a Y for Alternate_Part, change it to Both

I have tried using:

SELECT
    CUM_PART_NUM,
    CASE WHEN Make_Buy = 'B' AND Make_Buy = 'M' THEN 'Both' ELSE Make_Buy END AS Make_Buy 

FROM    
    BOM

But get:

CUM_PART_NUM    Make_Buy
IEO333  B
IEO333  M
IEO333  B
IEO333  M

I've set up a SQLFIDDLE for demonstration.

How can I aggregatre if a CUM_PART_NUM meets the condition?

Upvotes: 0

Views: 45

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Something like this:

select Upper_Level_Part_Number, CUM_PART_NUM,
       (case when min(Alternate_Part) = max(Alternate_Part)
             then min(Alternate_Part) else 'Both'
        end) as Alternate_Part,
       (case when min(Make_Buy) = max(Make_Buy)
             then min(Make_Buy) else 'Both'
        end) as Make_Buy,
       Quantity_Needed
from t
group by Upper_Level_Part_Number, CUM_PART_NUM, Quantity_Needed;

Upvotes: 3

Related Questions