Avatar of cindyfiller
cindyfiller
Flag for United States of America asked on

SQL code is not summarizing totals by group

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'))
Microsoft SQL Server 2008

Avatar of undefined
Last Comment
cindyfiller

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
John_Vidmar

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
cindyfiller

ASKER
You are right.  I know better than that - too many things going on.  Thanks for the catch on this.
cindyfiller

ASKER
Very quick reply and perfect answer.
PortletPaul

in support only:

a "having clause" is for conditions that can only exist after the group by is applied
e.g. having sum(value) > 100
that sum(...) can only be evaluated after grouping the data
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
John_Vidmar

That was quick, on my screen the date/times look like I answered before you asked the question?
Asked on	2014-05-21 at 16:17:14 ID: 28439127
Answered	2014-05-21 at 14:55:48 ID: 40082022

Open in new window

PortletPaul

you are a qualified mind reader? very useful
cindyfiller

ASKER
Hey - I needed an answer fast and you gave it super fast!  Just didn't realize how fast.  Thanks again for the assist.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.