sqlpostgresqlsum

Calculate sum of int column based on count of another column

Assume I have the following tables and relationship:

fruit
  price: int

purchase
  fruitId: FK
  numberPurchased: int

What I'm trying to do is calculate the total money spent on all purchases - where the price is set by the fruit table, but the purchase table knows how many fruits were purchased per record. Is this possible via SQL? Or do I have to fetch all the records, then add them up in the code instead?

I'm guessing I have to use the SUM function but I'm not sure how to multiply the price by the numberPurchased from the purchase table.

Solution

  • Assuming fruit has an id column, you need:

    select sum(p.numberPurchased * f.price)
from fruit f
join purchase p on f.id = p.fruitId;

    You can omit prefixes (p., f.) if the field names do not repeat.