sql - What's the best way of using Case When Statement for 100 or more products -

here table "sellers"

+------------+---------+----------+ | seller_id  | product |  units   | +------------+---------+----------+ | seller_123 |   a1    |   10     | | seller_123 |   b2    |   20     | | seller_123 |   c3    |   70     | +------------+---------+----------+ 

from here can see "seller_123" main product group c3 because units focus in c3 70 units. here trying achieve

+------------+-------------+ | seller_id  | main_product|  +------------+-------------+ | seller_123 |      c3     |   +------------+-------------+ 

here current sql query

with temp (     select seller_id         ,sum(case                  when product = 'a1'                     units                 end) a1_units         ,sum(case                  when product = 'b2'                     units                 end) b2_units         ,sum(case                  when product = 'c3'                     units                 end) c3_units     sellers     group seller_id      ) select seller_id     ,case          when a1_units > b2_units             , a1_units > c3_units             a1         when b2_units > a1_units             , b2_units > c3_units             b2         when c3_units > a1_units             , c3_units > b2_units             c3         end main_product temp 

what better way of doing this? if have 100 products, cannot case when in case?

thank help.

try this

    select  s1.seller_id, s1.product main_product      seller s1,     (select seller_id, max(units) max_unit seller     group seller_id) s2     s1.seller_id = s2.seller_id     , s1.units=s2.max_unit 

the tables below.

    select * seller;      seller_id   product units     seller_123   a1     10     seller_123   b2     20     seller_123   c3     70     seller_456   d1     10     seller_456   e2     20 

upon running given select statement, data below

    select  s1.seller_id, s1.product main_product      seller s1,     (select seller_id, max(units) max_unit seller     group seller_id) s2     s1.seller_id = s2.seller_id     , s1.units=s2.max_unit       seller_id   main_product     seller_456   e2     seller_123   c3