SQL Dealing with NULL Values

Question

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

I need the query to pull forward the last non-null value for Prod1 so that I can do: SELECT Person, SUM(Price) AS PriceSum FROM Table GROUP BY Person — jstrebor
– jstrebor, Commented Jan 14, 2022 at 18:39
Replace the nulls with the average price per product first. Then run that SQL command. — JJFord3
– JJFord3, Commented Jan 14, 2022 at 18:47

Stu · Accepted Answer · 2022-01-14 20:18:00Z

1

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

edited Jan 14, 2022 at 20:18

answered Jan 14, 2022 at 18:49

Stu

32.7k6 gold badges17 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

qTzz · Accepted Answer · 2022-01-14 18:45:02Z

0

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

answered Jan 14, 2022 at 18:45

qTzz

1266 bronze badges

Collectives™ on Stack Overflow

SQL Dealing with NULL Values

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related