Search code examples
mysqlsqladminer

MYSQL: adding values based on string values in other cells

Let's say we have table like this:

Car Colour Red colour car price Black colour car price
Brand1 Red 20000 25000
Brand2 Red 32000 34000
Brand2 Black 32000 34000
Brand2 Black 32000 34000

There is set prices for 2 different colours for each car brand. Prices are different for each brand. I need to find how much different brand cars cost in total. So, for Brand1 it'll be 20000 and for Brand2 32000 + 34000 + 34000 = 100000

Final table should look like this:

Car Total cost
Brand1 20000
Brand2 100000

I don't know how to do that and any help is welcome.

Solution

  • Hmmm . . . I think you want conditional aggregation:

    select car,
       sum(case when color = 'Red' then red_color_price
                when color = 'Black' then black_color_price
           end)
from t
group by car;