Calculate a column based on if else condition

I am trying to calculate the discounted cost for an account based on its product codes where different codes get different discounts. I am struggling to find a way where I can modify the query so that it will give me the right results in one query rather than me running the same query multiple times and changing the discount and product code.

As an example if the code is AmazonEC2 then apply 5% to the cost if the code is AmazonS3 then apply 3% to the cost, else apply 1% to the rest.

SELECT line_item_usage_account_id
    ,sum(line_item_unblended_cost) AS cost
    ,sum(line_item_unblended_cost * 0.05) AS discounted_cost
    ,sum(line_item_usage_amount) AS usage
    ,CAST(line_item_usage_start_date AS DATE) AS start_day
    ,CAST(line_item_usage_end_date AS DATE) AS end_day
    ,line_item_product_code
FROM cost_management
WHERE line_item_usage_account_id IN ('330')
    AND line_item_usage_start_date BETWEEN DATE '2019-03-01'
        AND DATE '2019-10-01'
    AND line_item_product_code = 'AmazonEC2'
GROUP BY line_item_usage_account_id
    ,CAST(line_item_usage_start_date AS DATE)
    ,CAST(line_item_usage_end_date AS DATE)
    ,line_item_product_code
HAVING sum(line_item_blended_cost) > 0
ORDER BY line_item_usage_account_id

Answer

If you want the information per account or per account per day, then I don’t think you want line_item_product_code in the GROUP BY.

In that case, you want conditional aggregation:

SELECT line_item_usage_account_id,
       SUM(line_item_unblended_cost) AS cost,
       SUM(CASE WHEN line_item_product_code = 'AmazonEC2' 
                THEN line_item_unblended_cost * 0.05
                WHEN line_item_product_code = 'AmazonS3'  
                THEN line_item_unblended_cost * 0.03
                ELSE ine_item_unblended_cost * 0.01
           END) As discounted_cost
       SUM(line_item_usage_amount) AS usage,
       CAST(line_item_usage_start_date AS DATE) AS start_day,
       CAST(line_item_usage_end_date AS DATE) AS end_day
FROM cost_management cm
WHERE line_item_usage_account_id IN ('330') AND
      line_item_usage_start_date BETWEEN DATE '2019-03-01' AND DATE '2019-10-01'
GROUP BY line_item_usage_account_id,
         CAST(line_item_usage_start_date AS DATE),
         CAST(line_item_usage_end_date AS DATE)
HAVING sum(line_item_blended_cost) > 0
ORDER BY line_item_usage_account_id