I am working on a code but it doesn't accept my join. I want my existing query to add the total user results from another query group by the same month and year. The code works until I try to add left join.

SELECT first_year, first_month, product_line, Users,
     SUM(CASE WHEN month_number = 0 THEN 1 ELSE 0 END) AS month_0,
       SUM(CASE WHEN month_number = 1 THEN 1 ELSE 0 END) AS month_1,
       SUM(CASE WHEN month_number = 2 THEN 1 ELSE 0 END) AS month_2,
       SUM(CASE WHEN month_number = 3 THEN 1 ELSE 0 END) AS month_3,
       SUM(CASE WHEN month_number = 4 THEN 1 ELSE 0 END) AS month_4,
       SUM(CASE WHEN month_number = 5 THEN 1 ELSE 0 END) AS month_5,
       SUM(CASE WHEN month_number = 6 THEN 1 ELSE 0 END) AS month_6,
       SUM(CASE WHEN month_number = 7 THEN 1 ELSE 0 END) AS month_7,
       SUM(CASE WHEN month_number = 8 THEN 1 ELSE 0 END) AS month_8

FROM (SELECT a.purchase_month, a.purchase_year,b.first_month,b.first_year, a.purchase_month-b.first_month as month_number, a.product_line
FROM (SELECT user_id, month(purchase_date) AS purchase_month, year(purchase_date) AS purchase_year, product_line
        FROM purchase) a,(SELECT user_id, month(first_purchase_date) AS first_month, year(first_purchase_date) as first_year, product_line 
                                        FROM first_purchase
                                        WHERE product_line="Restaurant") b
WHERE a.user_id=b.user_id AND purchase_year=first_year
HAVING a.product_line="Restaurant") AS with_month_number
LEFT JOIN (SELECT COUNT(user_id) AS first_users, year(first_purchase_date) as first_year, month(first_purchase_date) AS first_month, product_line
            FROM first_purchase
            WHERE product_line="Restaurant"
            GROUP BY year(first_purchase_date), month(first_purchase_date), product_line) AS j
ON first_year=j-year(first_purchase_date) AND first_month=j.month(first_purchase_date)
GROUP BY first_year, first_month, product_line
ORDER BY first_year, first_month;

CodePudding user response：

Having good indentation can significantly help see what is associated with what. I commented within such as issue with HAVING clause without any group by, ambiguous columns for join, and final group by since both tables had same column names. SQL Needs to know which. When in doubt and you have multiple tables, ALWAYS use table.column or alias.column in queries.

SELECT 
        first_year, 
        first_month, 
        product_line, 
        Users,
        SUM(CASE WHEN month_number = 0 THEN 1 ELSE 0 END) AS month_0,
        SUM(CASE WHEN month_number = 1 THEN 1 ELSE 0 END) AS month_1,
        SUM(CASE WHEN month_number = 2 THEN 1 ELSE 0 END) AS month_2,
        SUM(CASE WHEN month_number = 3 THEN 1 ELSE 0 END) AS month_3,
        SUM(CASE WHEN month_number = 4 THEN 1 ELSE 0 END) AS month_4,
        SUM(CASE WHEN month_number = 5 THEN 1 ELSE 0 END) AS month_5,
        SUM(CASE WHEN month_number = 6 THEN 1 ELSE 0 END) AS month_6,
        SUM(CASE WHEN month_number = 7 THEN 1 ELSE 0 END) AS month_7,
        SUM(CASE WHEN month_number = 8 THEN 1 ELSE 0 END) AS month_8
    FROM 
        (   SELECT 
                    a.purchase_month, 
                    a.purchase_year,
                    b.first_month,
                    b.first_year, 
                    a.purchase_month - b.first_month as month_number, 
                    a.product_line
                FROM 
                    (   SELECT 
                                user_id, 
                                month(purchase_date) AS purchase_month, 
                                year(purchase_date) AS purchase_year, 
                                product_line
                            FROM 
                                purchase 
                                -- you had HAVING her, but no GROUP BY, so just pulled into WHERE clause
                            WHERE
                                product_line = 'Restaurant' ) a,
                    (   SELECT
                                user_id, 
                                month(first_purchase_date) AS first_month, 
                                year(first_purchase_date) as first_year, 
                                product_line 
                            FROM 
                                first_purchase
                            WHERE 
                                product_line = 'Restaurant' ) b
                WHERE 
                        a.user_id = b.user_id 
                    AND a.purchase_year = b.first_year ) AS with_month_number

            LEFT JOIN 
            (   SELECT 
                        COUNT(user_id) AS first_users, 
                        year(first_purchase_date) as first_year, 
                        month(first_purchase_date) AS first_month, 
                        product_line
                    FROM 
                        first_purchase
                    WHERE 
                        product_line = 'Restaurant'
                    GROUP BY 
                        year(first_purchase_date), 
                        month(first_purchase_date), 
                        product_line ) AS j
                -- ambiguity here on JOIN...
                -- should it be with_month_number.first_year and with_month_number.first_month
                --ON first_year = j - year(first_purchase_date) 
                --AND first_month = j.month(first_purchase_date)

                -- changed to
                ON with_month_number.first_year = j.first_year 
                AND with_month_number.first_month = j.first_month
    GROUP BY 
        with_month_number.first_year, 
        with_month_number.first_month, 
        with_month_number.product_line
    ORDER BY 
        with_month_number.first_year, 
        with_month_number.first_month;