CODEWITHSUNDEEP
sqlnull
jstrebor
jstrebor

Reputation: 1

SQL Dealing with NULL Values

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:

Person Product Price
Bob Prod1 10.99
Sue Prod1 10.99
Bob Prod1
Anna Prod2 50.99

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:

Person Product Price
Bob Prod1 21.98
Sue Prod1 10.99
Anna Prod2 50.99

Upvotes: 0

Views: 60

Answers (2)

Stu
Stu

Reputation: 32579

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

Upvotes: 1

qTzz
qTzz

Reputation: 126

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

Upvotes: 0

Related Questions