Sum of different column in SQL

Question

We have an existing table with columns:

ItemCode nvarchar
Supplier1Price nvarchar
Supplier2Price nvarchar
Supplier3Price nvarchar
SelectedSupplier int (1, 2 or 3)

The table is used for canvassing from different suppliers. What I want to do is to get the sum of same items from selected suppliers.

Example:

ItemCode   Supplier1Price  Supplier2Price  Supplier3Price  SelectedSupplier
item-00    100             200             300             1
item-00    200             100             300             2
item-00    200             100             300             2
item-01    200             300             100             3
item-01    200             100             300             2

Result should be:

ItemCode  Total
item-00   300   
item-01   200

What I did is this:

select 
    ItemCode, 
    sum(SupplierPrice) as Total
from 
    (select 
        ItemCode,
        case SelectedSupplier
            when 1 then Supplier1Price
            when 2 then Supplier2Price
            when 3 then Supplier3Price
        end) as SupplierPrice
     from CanvassTable)
 group by ItemCode

Note: First, code above selects all itemcodes and corresponding price (from selected supplier). The result then will be processed in order to get the sum of prices of each item.

It's working already, the problem is, I used subquery and I worry that when the table data grows the query will have poor performance. My question is is there any way I can do this without subquery?

Answer 1

If your query works, why not just do:

select 
    ItemCode,
    SUM(case SelectedSupplier
        when 1 then Supplier1Price
        when 2 then Supplier2Price
        when 3 then Supplier3Price
    end) as SupplierPrice
from CanvassTable
group by ItemCode