CODEWITHSUNDEEP
sqlt-sql
Christopher
Christopher

Reputation: 788

JOIN CTE's Grouped by Month/Year

I have multiple CTEs which result in the following common table structure:

Year | Month | Total_Purchases_Product_Line_X

These represent purchases grouped by month & year across several product lines.

Ex.)

SELECT * FROM cte_line_x    

Year | Month | Total_Purchases_Product_Line_X
2018    01      256
2018    02      192

SELECT * FROM cte_line_y    

Year | Month | Total_Purchases_Product_Line_Y
2018    01      76
2018    02      59

I'd like to create something like the following 

Year | Month | Total_Purchases_Line_X | Total_Purchases_Line_Y | Total_Purchases_Line_Z
2018    01     256                       76
2018    02     192                       59

Where the total purchases of each product line is joined. However, I'm running into issues grouping the dates from each CTE after I have joined them together. Here is what I've tried:

SELECT 
    cte_product_x.Month, 
    cte_product_x.Year, 
    cte_product_x.total as Total_X, 
    cte_product_y.total as Total_Y, 
    cte_product_z.total as Total_Z
FROM 
     cte_product_x
LEFT JOIN
     cte_product_y ON
     cte_product_y.year = cte_product_x.year
     AND
     cte_product_y.month = cte_product_x.month
LEFT JOIN
     cte_product_z ON
     cte_product_z.year = cte_product_x.year
     AND
     cte_product_z.month = cte_product_x.month
 GROUP BY 
    cte_product_x.Month, 
    cte_product_x.Year
  ORDER BY 
    cte_product_x.Month, 
    cte_product_x.Year

I tried changing my SELECT to: 

SELECT 
    cte_product_x.Month, 
    cte_product_x.Year, 
    MAX(cte_product_x.total as Total_X), 
    MAX(cte_product_y as Total_Y), 
    MAX(cte_product_z as Total_Z)

However, it only worked for "Total_X". The counts for the other columns were the max value found for a grouped total for all months. I don't understand why.

Upvotes: 0

Views: 115

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270513

Doesn't this work?

SELECT x.Month, x.Year, x.total as Total_X, 
       y.total as Total_Y, z.total as Total_Z
FROM cte_product_x x JOIN
     cte_product_y y
     ON y.year = x.year AND y.month = x.month JOIN
     cte_product_z z
     ON z.year = x.year AND z.month = x.month
ORDER BY x.Month, x.Year;

At least it works for your sample data.

Upvotes: 2

Related Questions