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

Hi,

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
FULL OUTER JOIN
(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
Elanders




elanders1Asked:
Who is Participating?
 
sdstuberCommented:
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
0
 
CluskittCommented:
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.
0
 
HainKurtSr. System AnalystCommented:
try

select
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
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
sdstuberCommented:
other than isnull vs coalesce,  isn't that just a duplicate of previous post ?
0
 
HainKurtSr. System AnalystCommented:
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...
0
 
elanders1Author Commented:
Perfect Sdstuber, exactly wath i was looking for.

Thanks a lot

Best Regards
Elanders
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.