How do I use an IF statement to replace NULL value?

Question

This is a question from my assignment and I have most of it done just can't get the IF statement to replace the NULL. Question: Write a SELECT statement that answers this question: What is the total quantity purchased for each product within each category? Return these columns The category_name column from the category table The product_name column from the products table The total quantity purchased for each product with orders in the Order_Items table Use the WITH ROLLUP operator to include rows that give a summary for each category name as well as a row that gives the grand total. Use the IF and GROUPING functions to replace null values in the category_name and product_name columns with literal values if they’re for summary rows.

My Answer So Far:

SELECT product_name, SUM((item_price - discount_amount) * quantity) AS Total_quanity_purchased

FROM products p

JOIN order_items ori ON p.product_id = ori.product_id

GROUP BY product_name WITH ROLLUP

If you have an answer, please don't put it in the question: Please edit your question to remove your answer and instead add a proper answer under "Your Answer" below. — Bohemian, Dec 06 '21 at 00:28
Which column returns `NULL`? You probably just need to do `IFNULL(colname,0)` on all of the column with `SUM()` aggregation. — FanoFN, Dec 06 '21 at 06:28
Does this answer your question? [MySql Query Replace NULL with Empty String in Select](https://stackoverflow.com/questions/9560723/mysql-query-replace-null-with-empty-string-in-select) — Martin, Dec 08 '21 at 20:38

score 0 · Answer 1 · answered Dec 08 '21 at 23:25

I think your rollup NULL you are referring to is the Product_Name after all categories. You can probably use an

IF( Product_Name is null, 'All Products', Product_Name )

In lieu of using IF(), the COALESCE() function returns the first non-null value which the above would result in

COALESCE( Product_Name, 'All Products' )

Now, your actual question states to include the categories too, so I would suspect the category too, so you'll need that join too. So, the modified query would be (including all columns properly table/alias.columnName context. Get used to it now to prevent ambiguity knowing which column associated with which table. Especially when you get to using the same table multiple times in the same query. Use the aliases.

SELECT 
      if( c.category_name is null, 'All Categories', c.category_name) Category,
      if( p.product_name is null, 'All Products', p.product_name ) Product, 
      SUM(ori.quantity) as Total_Qty_Purchased,
      SUM(( ori.item_price - ori.discount_amount) * ori.quantity) 
         AS Total_Purchase_Sales
   FROM 
      products p
         JOIN Category c
            on p.category_id = c.category_id
         JOIN order_items ori 
            on p.product_id = ori.product_id

   GROUP BY 
      c.category_name,
      p.product_name,
      WITH ROLLUP

Finally, your computation of (price - discount) * qty would be a total PRICE, not Total_Quantity_Purchased.... Maybe a TotalPurchaseAmount, but not total Quantity... I added a separate Qty total as well. You can remove from your final answer, but just to show context.

How do I use an IF statement to replace NULL value?

1 Answers1