I want to merge these two tables into one. The idea is to create a table by month and show: month, number of unique customers who bought, number of invoices, number of products, total income, total income from product A

I'm having trouble adding the total income from product A per month since the table has two rows while the other results have four.

Example of table:

My query so far:

SELECT 
    MONTH(date) AS month, 
    COUNT (DISTINCT customerId) AS numOfCustomers, 
    SUM(income) AS sumOfIncome, 
    COUNT(invoiceId) AS numOfInvoice,
    COUNT(productId) AS numOfProduct
FROM 
    x 
WHERE 
    YEAR(date) = 2016 
GROUP BY 
    MONTH(date)

SELECT 
    MONTH(date) AS month,  
    SUM(income) AS sumOfIncomeA
FROM 
    x
WHERE 
    (productId) = 'A' 
    AND YEAR(date) = 2016
GROUP BY
    MONTH(date)

CodePudding user response：

Like I wrote in the comment, as both tables could not have the same month present you need a FULL OUTER JOIN

so

The COALESCE for month is needed, as it could be that one of the month coulb be NULL

SELECT
    COALESCE(t1.month,t2-month),
    t1.numOfCustomers,t1.sumOfIncome,t1.numOfInvoice,t1.numOfProduct
    ,t2.sumOfIncomeA
FROM
(SELECT 
    MONTH(date) AS month, 
    COUNT (DISTINCT customerId) AS numOfCustomers, 
    SUM(income) AS sumOfIncome, 
    COUNT(invoiceId) AS numOfInvoice,
    COUNT(productId) AS numOfProduct
FROM 
    x 
WHERE 
    YEAR(date) = 2016 
GROUP BY 
    MONTH(date)) t1
FULL  OUTER JOIN
(SELECT 
    MONTH(date) AS month,  
    SUM(income) AS sumOfIncomeA
FROM 
    x
WHERE 
    (productId) = 'A' 
    AND YEAR(date) = 2016
GROUP BY
    MONTH(date)) t2 ON t1.month = t2.month

CodePudding user response：

Here's a solution that first creates a big list of months. You can modify the "months" CTE to go back as far as you need. By default, this query will go back 83 years from today. After you have a good list of months, then you can join your data to it so that you are guaranteed to have all the months, and only sales data if present.

--First CTE "x" is used to create a sequence of 10 numbers.
WITH x as (
    SELECT * FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as x(a)
)
--Second CTE "y" creates a sequence of 1000 numbers.
, y as (
    SELECT ROW_NUMBER() OVER(ORDER BY hundreds.a, tens.a, ones.a) as row_num
    FROM x as ones, x as tens, x as hundreds
)
--Third CTE "months" creates a sequence of months going back in time from today.
--To go farther back than 1000 months, modify the "y" CTE to have a "thousands" (or more) table(s).
, months as (
    SELECT
        YEAR(DATEADD(month, -1 * y.row_num, GETDATE())) as [year]
        , MONTH(DATEADD(month, -1 * y.row_num, GETDATE())) as [month]
        , CAST(YEAR(DATEADD(month, -1 * y.row_num, GETDATE())) as nvarchar(6))
            RIGHT('00'   CAST(MONTH(DATEADD(month, -1 * y.row_num, GETDATE())) as nvarchar(6)),2) as YEAR_MONTH
    FROM y
)   

--Main select. 
--First FROM is a list of months so that we know for a fact we have all the months in the year.
--Then do a LEFT OUER JOIN to your main data. All months will be returned.
--If there is no match in the data table, then the value will be null. 
--You can use an ISNULL(SUM(x.income),0) to convert nulls to 0.
SELECT 
    m.[month] AS month, 
    COUNT (DISTINCT x.customerId) AS numOfCustomers, 
    SUM(x.income) AS sumOfIncome, 
    COUNT(x.invoiceId) AS numOfInvoice,
    COUNT(x.productId) AS numOfProduct
FROM months as m
    LEFT OUTER JOIN x
        ON YEAR(x.[date]) = m.[year]
        AND MONTH(x.[date]) = m.[month]
WHERE 
    x.YEAR([date]) = 2016 
GROUP BY 
    m.MONTH([date])