Question 339946 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How do I use the If grouping function to eliminate null values in a row

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.

select 
    categories.category_name
  , products.product_name
  , sum(order_items.quantity) as total_qty_purchased

from categories
inner join products on products.category_id = categories.category_id
inner join order_items on order_items.product_id = products.product_id
group by categories. category_name, product_name with rollup;

This returned null values. I’m expected to eliminate the null value rows using the if grouping function, but I don’t know how to do that.

Tags:

2

Answers


  1. 0

    You can eliminate the null values by having clause,

    select 
    categories.category_name
  , products.product_name
  , sum(order_items.quantity) as total_qty_purchased

from categories
inner join products on products.category_id = categories.category_id
inner join order_items on order_items.product_id = products.product_id
group by categories. category_name, product_name having sum(order_items.quantity) is not null
    Login or Signup to reply.
  2. 0

    Use IF for the category name, since this is a two-way condition. It’s only null for the grand total.

    Use CASE for the product name because there are three possibilities: it will be null for the grand total or category subtotal, non-null for the product total. For the grand total you don’t need to put anything in the product name column of the result, because the category has already been replaced with Grand Total.

    select 
    IF(c.category_name IS NULL, 'Grand Total', c.category_name) AS category_name
  , CASE 
        WHEN c.category_name IS NULL AND p.product_name IS NULL THEN ''
        WHEN p.product_name IS NULL THEN 'Subtotal'
        ELSE p.product_name
    END AS product_name
  , sum(oi.quantity) as total_qty_purchased

from categories AS c
inner join products AS p on p.category_id = c.category_id
inner join order_items AS oi on oi.product_id = p.product_id
group by c.category_name, p.product_name with rollup;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search