Link to home
Create AccountLog in
Avatar of cindyfiller
cindyfillerFlag 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'))
ASKER CERTIFIED SOLUTION
Avatar of John_Vidmar
John_Vidmar
Flag of Canada image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of cindyfiller

ASKER

You are right.  I know better than that - too many things going on.  Thanks for the catch on this.
Very quick reply and perfect answer.
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
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

you are a qualified mind reader? very useful
Hey - I needed an answer fast and you gave it super fast!  Just didn't realize how fast.  Thanks again for the assist.