Sum dataset in SQL with conditional expressions

Question

I'm working with a dataset of items with different values and I would like a SQL query to calculate the total USD value of the dataset.

Example Dataset:

id     |  type     |  numOrdered 
0      |  apple    |   1   
1      |  orange   |   3   
2      |  apple    |   10  
3      |  apple    |   5   
4      |  orange   |   2   
5      |  apple    |   1   

Consider this dataset of fruit orders. Let's say apples are worth $1 and oranges are worth $2. I would like to know how much total USD in fruit orders we have.

I'd like to perform the same operation as this example Javascript function, but using SQL:

let sum = 0;
for(let fruitOrder of fruitOrders) {
   if(fruitOrder.type == "orange"){
      sum += fruitOrder.numOrdered*2;
   } else {
      sum += fruitOrder.numOrdered*1;
   }
}
return sum;

So the correct answer for this dataset would be $27 USD total since there are 17 apples worth $1 and 5 oranges worth $2.

I know how to break it down into two distinct queries giving me the number I want split by type

SELECT 
sum("public"."fruitOrders"."num"*2) AS "sum"
FROM "public"."fruitOrders"
WHERE "public"."fruitOrders"."type" = 'orange';

which would return $10, the total USD value of oranges

SELECT 
sum("public"."fruitOrders"."num") AS "sum"
FROM "public"."fruitOrders"
WHERE "public"."fruitOrders"."type" = 'apple';

which would return $17, the total USD value of apples

I just don't know how to sum those numbers together in SQL to get $27, the total USD value of the dataset.

Answer 1

If you want the values 1 and 2 hardcoded then you can use a CASE statement with SUM():

SELECT 
  sum(case type 
        when 'apple' then 1
        when 'orange' then 2
      end * numordered
  ) AS "sum"
FROM "public"."fruitOrders"

See the demo.
Result:

| sum |
| --- |
| 27  |