How do you sum on top of a group by

[workweek]

How do you do a sum on top of a group by or how do you combine an if
statement with a group by? My code looks like this:

SELECT t_Starts_AppFlow.[Organization Level 1 : R],
t_Starts_AppFlow.[Location Level 3 : R], t_Starts_AppFlow.[EEO/AAP],
Count(t_Starts_AppFlow.[Step Name : CSW]) AS Hire,
Sum(t_Starts_AppFlow!Female) AS [Hire Female], Sum(t_Starts_AppFlow!POC) AS
[Hire POC], Sum(t_Starts_AppFlow!Manager) AS [Hire Manager],
Sum(t_Starts_AppFlow![Non-Manager]) AS [Hire Non-Manager],
Sum(t_Starts_AppFlow!Engineer) AS [Hire Engineer],
Sum(t_Starts_AppFlow![Non-Engineer]) AS [Hire Non-Engineer],
Sum(IIf(t_Starts_AppFlow![Internal Application] Not Like "",1,0)) AS [Hire
Total Applicants], Sum(t_Starts_AppFlow!Internal) AS [Hire Internal],
Sum(t_Starts_AppFlow!External) AS [Hire External]
FROM t_Starts_AppFlow
GROUP BY t_Starts_AppFlow.[Organization Level 1 : R],
t_Starts_AppFlow.[Location Level 3 : R], t_Starts_AppFlow.[EEO/AAP],
t_Starts_AppFlow.[Step Name : CSW]
HAVING (((t_Starts_AppFlow.[Step Name : CSW])="Hire"));

I need a sum by the Organization Level 1 : R.

 

[John Spencer]

If you need a grand total sum by Organization Level 1 : R then you need to
remove some of the fields that are in the GROUP BY and SELECT clauses. Your
query to do this would look like the following. NOTE: I also changed your IIF
so to handle Nulls and zero-length strings. There is no reason to use the
LIKE comparison operator if you are not using any wild cards. I also moved
the HAVING clause into a WHERE clause for efficiency. You should use a HAVING
clause when you wish to filter by the results of an aggregation (Sum, Count)
and a WHERE clause when you want to filter the records that will be aggregated.

SELECT t_Starts_AppFlow.[Organization Level 1 : R]
, Count(t_Starts_AppFlow.[Step Name : CSW]) AS Hire
, Sum(t_Starts_AppFlow!Female) AS [Hire Female]
, Sum(t_Starts_AppFlow!POC) AS [Hire POC]
, Sum(t_Starts_AppFlow!Manager) AS [Hire Manager]
, Sum(t_Starts_AppFlow![Non-Manager]) AS [Hire Non-Manager]
, Sum(t_Starts_AppFlow!Engineer) AS [Hire Engineer]
, Sum(t_Starts_AppFlow![Non-Engineer]) AS [Hire Non-Engineer]
, Sum(IIf(t_Starts_AppFlow![Internal Application] <> "",1,0)) AS [Hire
Total Applicants]
, Sum(t_Starts_AppFlow!Internal) AS [Hire Internal]
, Sum(t_Starts_AppFlow!External) AS [Hire External]

FROM t_Starts_AppFlow

WHERE t_Starts_AppFlow.[Step Name : CSW]="Hire"

GROUP BY t_Starts_AppFlow.[Organization Level 1 : R]



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County