sql - SELECTING aggregate data using CASE statement issue -

i'm trying create query obtain zone data in preparation moving column table. how our db set have assets , cables. 1 asset can have many cables cable cannot have multiple assets. we're trying move zone field cable table (ex. below) asset table.

a_id    c_id       zone         -------------------------- 1       1          green 1       2          green    1       3          yellow 2       4          green 2       5          red 3       6          yellow 3       7          yellow 3       8          yellow 3       9          red 

the way want set asset table if asset contains multiple cables different zones, if 1 of zones yellow defaults yellow (ex. 3 green cables, 1 yellow cable - asset_id has yellow zone). next if doesn't have yellows has @ least 1 red, defaults red (ex. 2 green cables, 3 red cables - asset_id has red zone). if has green zones defaults green.

using sample table above these results expect.

expected results      a_id   zone ------------- 1      yellow 2      red 3      yellow 

i'm trying use case statements i'm having difficulty formulating query group results correctly.

any appreciated, thank in advance.

one way using conditional aggregation , case expression.

select a_id ,case when yellow_count >=1 'yellow'       when yellow_count = 0 , red_count >=1 'red'       when yellow_count = 0 , red_count = 0 , green_count >=1 'green'  end zone (select a_id,       count(case when zone = 'yellow' 1 end) yellow_count,       count(case when zone = 'red' 1 end) red_count       count(case when zone = 'green' 1 end) green_count       cable_table       group a_id) t