troubleshooting Question

SQL code is not summarizing totals by group

Avatar of cindyfiller
cindyfillerFlag for United States of America asked on
Microsoft SQL Server 2008
7 Comments1 Solution282 ViewsLast Modified:
I know I'm missing something simple, but am not seeing it.  I'm starting with a table (called view_project_with_attributes) and it has one record per fund - a total of about 4100 funds.  I'm trying to bring in the balances for a couple of account codes so have done a sub query where I am summing the dollars and grouping by the projectdimid (which is the fund).  Instead of getting 4100 records, I'm getting over a million.  I've tried doing this code multiple ways and it just isn't working.  What am I missing?

SELECT        View_Project_with_Attributes.ProjectDimID, View_Project_with_Attributes.ProjectID, View_Project_with_Attributes.ProjectDescription,
                         View_Project_with_Attributes.ProjClass, View_Project_with_Attributes.ProjCollege, View_Project_with_Attributes.ProjDept, Q1400.Balance1400,
                         Q1405.Balance1405

FROM            View_Project_with_Attributes
LEFT OUTER JOIN
                             (SELECT        ProjectDimID, SUM(NaturalAmount) AS Balance1400
                               FROM            FACT_GLTransactionDistribution
                               WHERE        (AccountCode = '1400') AND (PostDate <= '3/31/14') AND (AccountCategorySystemID IN ('1'))
                               GROUP BY ProjectDimID) AS Q1400 ON View_Project_with_Attributes.ProjectDimID = Q1400.ProjectDimID
LEFT OUTER JOIN
                             (SELECT        ProjectDimID, SUM(NaturalAmount) AS Balance1405
                               FROM            FACT_GLTransactionDistribution AS FACT_GLTransactionDistribution_1
                               WHERE        (AccountCode = '1405') AND (PostDate >= '7/1/01') AND (PostDate <= '3/31/14') AND (AccountCategorySystemID IN ('1'))
                               GROUP BY ProjectDimID) AS Q1405 ON View_Project_with_Attributes.ProjectDimID = Q1400.ProjectDimID

HAVING        (View_Project_with_Attributes.ProjClass IN ('Endowed', 'Quasi Endowed', 'Endowed - Non Invested'))
ASKER CERTIFIED SOLUTION
John_Vidmar

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros