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

Add missing values in each group using presto or mysql

I have a following table :

prod_name - quantity - group
tv        - 30  - base 
microwave - 10  - base 
watch     - 5   - base  
phone     - 25  - base 
washer    - 15  - base 
dryer.    - 14  - base  
microwave - 7   - inventory_2021 
phone     - 16  - inventory_2021  
tv        - 30  - inventory_2022 
watch     - 5   - inventory_2022 
phone     - 25  - inventory_2022

I want to fill/ enter missing records from base(group) to all other groups

Expected output :

tv        - 30  - base 
microwave - 10  - base 
watch     - 5   - base 
phone     - 25  - base 
washer    - 15  - base 
dryer.    - 14  - base 
tv        - 30  - inventory_2021 
microwave - 7   - inventory_2021 
watch     - 5   - inventory_2021 
phone     - 16  - inventory_2021 
washer    - 15  - inventory_2021 
dryer.    - 14  - inventory_2021 
tv        - 30  - inventory_2022 
microwave - 10  - inventory_2022 
watch     - 5   - inventory_2022 
phone     - 25  - inventory_2022 
washer    - 15  - inventory_2022 
dryer.    - 14  - inventory_2022

So in the output I have same number of entries in each group and total number of entries should match the base group.

Tags:

2

Answers


  1. 0

    Use CROSS JOIN to create all the missing rows. Then you can LEFT JOIN this with the original table to fill in the gaps.

    WITH all_prods_and_groups AS (
    SELECT p1.prod_name, p1.quantity, p2.group
    FROM products AS p1
    CROSS JOIN (
        SELECT DISTINCT `group`
        FROM products
    ) AS p2
    WHERE p1.group = 'base'
)
SELECT t1.prod_name, 
    IFNULL(t2.quantity, t1.quantity) AS quantity, 
    IFNULL(t2.group, t1.group) AS `group`
FROM all_prods_and_groups AS t1
LEFT JOIN products AS t2 ON t1.prod_name = t2.prod_name AND t1.group = t2.group

    DEMO

    Login or Signup to reply.
  2. 0

    MySQL and Presto/Trino are two a bit SQL different dialects. The basic idea is the same – build the "base" cartesian product of products and groups. If you are sure that all products are present in base you can go simply with the following for Presto/Trino:

    -- sample data
WITH dataset(prod_name, quantity, "group") as (
    values ('tv'       , 30, 'base'),
        ('microwave', 10, 'base'),
        ('watch'    , 5 , 'base'),
        ('phone'    , 25, 'base'),
        ('washer'   , 15, 'base'),
        ('dryer'   , 14, 'base'),
        ('microwave', 7 , 'inventory_2021'),
        ('phone'    , 16, 'inventory_2021'),
        ('tv'       , 30, 'inventory_2022'),
        ('watch'    , 5 , 'inventory_2022'),
        ('phone'    , 25, 'inventory_2022')
),
-- query parts
base_products AS (
    SELECT prod_name, quantity
    FROM dataset AS p1
    WHERE "group" = 'base' -- select all base products
),
all_groups as (
    SELECT distinct "group" grp
    FROM dataset
),
base_all as(
    SELECT b.prod_name, b.quantity, grp -- build base lookup table
    FROM base_products b
    CROSS JOIN all_groups
)

select b.prod_name,
    coalesce(d.quantity, b.quantity) quantity, -- use "original" quantity
    b.grp "group"
from base_all b
left join dataset d on b.prod_name = d.prod_name and b.grp = d."group"
;

    Output:

    prod_name quantity group
    tv 30 inventory_2021
    microwave 7 inventory_2021
    watch 5 inventory_2021
    phone 16 inventory_2021
    washer 15 inventory_2021
    dryer 14 inventory_2021
    tv 30 base
    microwave 10 base
    watch 5 base
    phone 25 base
    washer 15 base
    dryer 14 base
    tv 30 inventory_2022
    microwave 10 inventory_2022
    watch 5 inventory_2022
    phone 25 inventory_2022
    washer 15 inventory_2022
    dryer 14 inventory_2022
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search