asked on

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

Cluskitt

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.

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

HainKurt

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

Sean Stuber

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

HainKurt

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...

elanders1

ASKER

Perfect Sdstuber, exactly wath i was looking for.

Thanks a lot

Best Regards
Elanders