SUM() and GROUP BY Customer and Year over two Table

Posted on 2011-10-26
Last Modified: 2012-05-12

I have two table factIncome_V and factBudget_V and i try to sum value from them grouped by Maincustomer and Year.

If i run this from factIncome_V:
SELECT fI.MainCustomerNo AS MainCustomerNo, YEAR(fI.InvoiceDate) AS IncomeYear, SUM(Income) AS IncomeYearValue FROM factIncome_V fI
WHERE fI.MainCustomerNo = '12651'
GROUP BY fI.MainCustomerNo, YEAR(fI.InvoiceDate)

I Get:
MainCustomerNo - IncomeYear - IncomeYearValue
12651  -  2009  -  316888
12651  -  2010  -  417146

And if i run this from factBudget_V:
SELECT fB.MainCustomerNo, YEAR(fB.BudgetDate) AS BudgetYear, SUM(Budget) AS BudgetYearValue FROM factBudget_V fB
WHERE fB.MainCustomerNo = '12651'
GROUP BY fB.MainCustomerNo, YEAR(fB.BudgetDate)

I Get:
MainCustomerNo - BudgetYear - BudgetYearValue
12651  -  2010  -  320000
12651  -  2011  -  390000

So fare so good, now i want to put it in one SQL so the Result Looks like this.
MainCustomerNo - myYear - IncomeYearValue - BudgetYearValue
12651  -  2009  -  316888  -  0
12651  -  2010  -  417146  -  320000
12651  -  2011  -  0  -  390000

Any idea on how to do this?

I tried this way byt it only generate the total by mainCustomerno.
SELECT Coalesce(fI.MainCustomerNo, fB.MainCustomerNo) AS MainCustomerNo, IncomeYearValue, BudgetYearValue FROM
(SELECT MainCustomerNo, SUM(Income) AS IncomeYearValue FROM factIncome_V GROUP BY MainCustomerNo) fI
(SELECT MainCustomerNo, SUM(Budget) AS BudgetYearValue FROM factBudget_V GROUP BY MainCustomerNo) fB ON fI.MainCustomerNo = fB.MainCustomerNo

I cant figure out how to get it to also group by year.

Please Help. :)

Kind regards

Question by:elanders1
    LVL 18

    Expert Comment

    SELECT fI.MainCustomerNo AS MainCustomerNo, YEAR(fI.InvoiceDate) AS IncomeYear, SUM(Income), SUM(Budget) AS BudgetYearValue AS IncomeYearValue FROM factIncome_V fI
    INNER JOIN factBudget_V fB ON fI.MainCustomerNo=fB.MainCustomerNo AND fI.InvoiceDate=fB.BudgetDate
    WHERE fI.MainCustomerNo = '12651'
    GROUP BY fI.MainCustomerNo, YEAR(fI.InvoiceDate)

    Baring typos, I think this is what you want.
    LVL 73

    Accepted Solution

    SELECT   COALESCE(fi.maincustomerno, fb.maincustomerno) AS maincustomerno,
             COALESCE(fi.incomeyear, fb.budgetyear) AS myyear,
             COALESCE(incomeyearvalue, 0) incomeyearvalue,
             COALESCE(budgetyearvalue, 0) budgetyearvalue
        FROM     (SELECT   fi.maincustomerno AS maincustomerno,
                           year(fi.invoicedate) AS incomeyear,
                           SUM(income) AS incomeyearvalue
                      FROM factincome_v fi
                     WHERE fi.maincustomerno = '12651'
                  GROUP BY fi.maincustomerno, year(fi.invoicedate)) fi
             FULL OUTER JOIN
                 (SELECT   fb.maincustomerno,
                           year(fb.budgetdate) AS budgetyear,
                           SUM(budget) AS budgetyearvalue
                      FROM factbudget_v fb
                     WHERE fb.maincustomerno = '12651'
                  GROUP BY fb.maincustomerno, year(fb.budgetdate)) fb
             ON fi.maincustomerno = fb.maincustomerno AND fi.incomeyear = fb.budgetyear
    ORDER BY maincustomerno, myyear
    LVL 51

    Expert Comment


    isnull(a.MainCustomerNo, b.MainCustomerNo) MainCustomerNo,
    isnull(a.IncomeYear, b.BudgetYear ) Year,
    isnull(BudgetYearValue,0) BudgetYearValue,
    isnull(IncomeYearValue,0) IncomeYearValue
    from (query1) a full join (query2) b on a.MainCustomerNo=b.MainCustomerNo and a.IncomeYear=b.BudgetYear
    LVL 73

    Expert Comment

    other than isnull vs coalesce,  isn't that just a duplicate of previous post ?
    LVL 51

    Expert Comment

    looks like yes :) I did not check previous posts in details :) or maybe I did not refresh the page before submitting mine... but, yes you are right...

    Author Closing Comment

    Perfect Sdstuber, exactly wath i was looking for.

    Thanks a lot

    Best Regards

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Suggested Solutions

    As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
    Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now