'Joining 2 Select Queries (one query including an aggregate function) from two different tables

I am trying to left join the two queries below (mock data for reference purposes).

SELECT
    marker_number AS ISN,
    IF(shelf_life_code = 'A', 1, 0) AS Store_Repaired,
    SUBSTRING(marker_number, 5, 9) AS item_number_id,
    IF(action_code = '1',1,0) AS Transferred_Main_Store,
    CAST(RIK AS INTEGER) AS Store_Days,
    process_date
FROM
    store_one.actions_table 
WHERE 
    action_ph_code = 'C8'
    AND tricche_ballak_code = 'NA'

SELECT 
    item_number_id,
    AVG(main_store_rpr_days)AS MainStore_Days,
FROM
    mstr_actions_table
GROUP BY 
    item_number_id

My initial approach, which doesn't work, is the following :

SELECT * 
FROM 
    (SELECT 
         marker_number AS ISN,
         IF (shelf_life_code = 'A', 1, 0) AS Store_Repaired,
         SUBSTRING(marker_number, 5, 9) AS item_number_id,
         IF (action_code = '1',1,0) AS Transferred_Main_Store,
         CAST(RIK AS INTEGER) AS Store_Days,
         process_date
     FROM
         store_one.actions_table 
     WHERE 
         action_ph_code = 'C8'
         AND tricche_ballak_code = 'NA') A
LEFT JOIN 
    (SELECT 
         item_number_id,
         AVG(main_store_rpr_days)AS MainStore_Days,
     FROM
         mstr_actions_table
     GROUP BY 
         item_number_id) B ON A.item_number_id = B.item_number_id

Any pointer would is highly appreciated.

sqlleft-joinaggregate-functionsdatabricks


Solution 1:[1]

    With A as(SELECT
    marker_number AS ISN,
    IF(shelf_life_code = 'A', 1, 0) AS Store_Repaired,
    SUBSTRING(marker_number, 5, 9) AS item_number_id,
    IF(action_code = '1',1,0) AS Transferred_Main_Store,
    CAST(RIK AS INTEGER) AS Store_Days,
    process_date
FROM
    store_one.actions_table 
WHERE 
    action_ph_code = 'C8'
    AND tricche_ballak_code = 'NA'),
B as(
SELECT 
    item_number_id,
    AVG(main_store_rpr_days)AS MainStore_Days,
FROM
    mstr_actions_table
GROUP BY 
    item_number_id
)
select A.*,B.MainStore_Days from A left join B on A.item_number_id=cast(B.item_number_id as varchar(10))

This could be the simplest solution.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Diganta Dey

Related Questions