How does the SQL version of SUMPRODUCT work?

unit_price * quantity multiplies the two values on every row, the way SUMPRODUCT pairs up the two ranges. SUM then adds up all of those products into one total. Dividing by the total quantity gives a weighted average, another common use of SUMPRODUCT.

Why can SQL give a different result from SUMPRODUCT?

SUMPRODUCT can include conditions inside the formula; in SQL add a WHERE clause instead.

SUMPRODUCT in SQL

SUMPRODUCT multiplies matching values in two ranges and adds up the results, such as price times quantity; SQL does this with SUM over a multiplication.

The Excel version

=SUMPRODUCT(B2:B100, C2:C100)

The SQL equivalent

This example assumes: order_items(id bigint, order_id bigint, unit_price numeric, quantity numeric). It is written in PostgreSQL syntax, and your table and column names will differ from these.

SELECT SUM(unit_price * quantity) AS total_revenue,
       SUM(unit_price * quantity) / NULLIF(SUM(quantity), 0) AS weighted_avg_price
FROM order_items;

How it works

unit_price * quantity multiplies the two values on every row, the way SUMPRODUCT pairs up the two ranges.
SUM then adds up all of those products into one total.
Dividing by the total quantity gives a weighted average, another common use of SUMPRODUCT.

Where the results differ from Excel

SUMPRODUCT can include conditions inside the formula; in SQL add a WHERE clause instead.
Null in either column makes that row's product null, and SUM skips it, whereas Excel treats a blank as zero.
Ranges must be the same size in Excel; in SQL each row already pairs its own values, so mismatches cannot happen.

Skip the query: ask in plain English

If you would rather not write SQL, type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. Check that the result matches how your spreadsheet defines the number.

What is total revenue from price times quantity across all order lines?
What is the quantity-weighted average price?