Rules to add a status to a column, based on subitems of that column

[cmck]

Hi all,

I have a dataset with a list on items, subitems, and the status of each individual subitem. Each item has at least one subitem, but can have multiple. Each subitem can either be in State 1, State 2 or State 3.

I want to show for each item a single status. For example if all the subitem statuses are in State 1, then I want the status for the item to be State 1. 

If however under an item there are subitems with different statuses, I want to take the lowest state. For example if an item has two subitems, in State 2 and State3, then I want the item status to be State 2. 

For example in the table below, the status for item 1 should be State 1, Item 2 status should be State 2 and Item 3 status should be State 1.

Item 1	I1 Subitem 1	State 3
Item 1	I1 Subitem 2	State 3
Item 2	I2 Subitem 1	State 2
Item 3	I3 Subitem 1	State 1
Item 1	I1 Subitem 3	State1
Item 2	I2 Subitem 2	State 2

Would appreciate any help you can give. Thanks.

[Adamboer]

You can achieve this by using a combination of GROUP BY and CASE statements. Here's an example query that should work:

SELECT
item,
CASE
WHEN MIN(status) = 1 THEN 'State 1'
WHEN MIN(status) = 2 THEN 'State 2'
WHEN MIN(status) = 3 THEN 'State 3'
END AS item_status
FROM
your_table
GROUP BY
item
In this query, we group the table by the item column, and use the CASE statement to determine the minimum status value for each item. We then use the MIN function to get the lowest status value for each item. Finally, we use the CASE statement again to assign a status string based on the minimum status value.

Note that if there are any null values in the status column, the query will return NULL as the item status. You may need to modify the query to handle null values if they are present in your data.