I have a quick question when dealing with null values in a table. I'm trying to sum up all sales made by each sales person below:

In this case, Bob didn't enter his price and left it null but it would be the same 10.99 value. How would I write a query that delivers total sales by sales person like below:

CodePudding user response：

You could use max (or min or avg) as a windowed aggregate to replace the nulls per person & product:

Select Person, Product, sum(Price) Price
from (
    select person, product, 
      Coalesce(price, Max(price) over(partition by person, product)) Price
    from t
)t
group by person, product

CodePudding user response：

SELECT Person, SUM(Price) AS PriceSum FROM Table GROUP BY Person should give you the desired results since you're grouping by Person they would be aggregated under Bob