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


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

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

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

Expert Comment

ID: 37031233

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 74

Expert Comment

ID: 37031245
other than isnull vs coalesce,  isn't that just a duplicate of previous post ?
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...

Author Closing Comment

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

Thanks a lot

Best Regards

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…

564 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