How to add the Value of only Null Data in SQL

Question

I have below data coming from join of tables:

UOM  SUM  PRODUCT      MARKING
KG   150  HAIR DRYER    HD
KG   150  null          HD
KG   7    HEAT GUN      HS
KG   3    HEAT GUN      HP

Now from the above data, i want to add the SUM Value of null Product to the Product that has the same Marking. For this i have written this query

SELECT MARKUP.MARKING, SUM(PRODUCT.PRODUCT_QUANTITY), MIN(PRODUCT.PRODUCTNAME), MIN(MEASURE.UOM) FROM
PRODUCT INNER JOIN MEASURE on PRODUCT.ID = MEASURE.PRODUCTID
INNER JOIN MARKUP on PRODUCT.MARKUPID=MARKUP.ID
INNER JOIN PRODUCTCODE on PRODUCTCODE.ID = PRODUCT.PRODUCTCODEID
WHERE PRODUCTCODE.ID = '1123'
GROUP BY MARKUP.MARKING
ORDER BY MARKUP.MARKING DESC

SO with this query, i am able to fetch the desired results as shown below

UOM  SUM  PRODUCT      MARKING
KG   300  HAIR DRYER    HD
KG   7    HEAT GUN      HS
KG   3    HEAT GUN      HP

But when the data is like below:

UOM  SUM  PRODUCT      MARKING
KG   102  HAIR DRYER    HD
KG   202  HEAT GUN      HD
KG   4    HAIR DRYER    HS
KG   6    HEAT GUN      HS

Then the above query adds the SUM column(i.e. the Product Quantity Column) based on Marking field as shown below

UOM  SUM  PRODUCT      MARKING
KG   304  HAIR DRYER    HD
KG   10   HAIR DRYER    HS

Which is totally wrong since i don't have any null Product so, adding the product is not required here based on Marking.

Please let me know how can i only add the SUM i.e. the Product Quantity if the Product value is null for the same Marking. If not then it should show all the products with their quantities.

Thanks in advance.

You say "data in my table". Then your query refers to three tables. — Gordon Linoff
– Gordon Linoff, Commented May 18, 2021 at 15:14

Gordon Linoff · Accepted Answer · 2021-05-18 15:15:21Z

1

Hmmm . . . You can use window functions and filtering:

select t.*,
       (sum + null_sum) as sum_with_nulls_if_any
from (select t.*,
             sum(case when product is null then sum else 0 end) over (partition by marking) as null_sum
      from t
     ) t
where product is not null;

answered May 18, 2021 at 15:15

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

umberto-petrov · Accepted Answer · 2021-05-18 15:44:11Z

1

What if there are multiple rows with non-null product for the same marking, should we add null products data to all of them? Assuming that yes, I think something like that should do it:

SELECT UOM, 
  SUM + (
    SELECT SUM(SUM)
    FROM Table
    WHERE MARKING = t.MARKING
      AND PRODUCT IS NULL
  ),
  PRODUCT,
  MARKING
FROM Table t
WHERE PRODUCT IS NOT NULL

answered May 18, 2021 at 15:44

umberto-petrov

7434 silver badges9 bronze badges

Collectives™ on Stack Overflow

How to add the Value of only Null Data in SQL

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