Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

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

Posted on 2011-10-26
Medium Priority
198 Views
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.

Kind regards
Elanders

0
Question by:elanders1

LVL 18

Expert Comment

ID: 37030721
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

LVL 74

Accepted Solution

sdstuber earned 2000 total points
ID: 37030751
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

LVL 61

Expert Comment

ID: 37031233
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

LVL 74

Expert Comment

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

LVL 61

Expert Comment

ID: 37031332
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

Author Closing Comment

ID: 37036024
Perfect Sdstuber, exactly wath i was looking for.

Thanks a lot

Best Regards
Elanders
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
###### Suggested Courses
Course of the Month11 days, 12 hours left to enroll