Need Help to do a GROUP SUM

[JRBower]

The SQL below displays an employee count for 14 branches.

What I'd like to do is keep the individual counts for branches 1 through 11 but have branch 12 be the sum of branch 12, 13, 14.


SELECT COUNT(Employees .EmployeeID) AS Count_EmployeeI D, Branches.Branch es, UserInRole.User RoleID
FROM dbo.Employees, dbo.Branches, dbo.UserInRole
WHERE dbo.Employees.B ranchID = dbo.Branches.Br anchID
AND dbo.Employees.U serRoleID = dbo.UserInRole. UserRoleID
GROUP BY Branches.Branch es, UserInRole.User RoleID
HAVING (UserInRole.Use rRoleID = '1')


Thanks for your help.

James

[azimmer]

The SQL below displays an employee count for 14 branches.

What I'd like to do is keep the individual counts for branches 1 through 11 but have branch 12 be the sum of branch 12, 13, 14.


SELECT COUNT(Employees .EmployeeID) AS Count_EmployeeI D, Branches.Branch es, UserInRole.User RoleID
FROM dbo.Employees, dbo.Branches, dbo.UserInRole
WHERE dbo.Employees.B ranchID = dbo.Branches.Br anchID
AND dbo.Employees.U serRoleID = dbo.UserInRole. UserRoleID
GROUP BY Branches.Branch es, UserInRole.User RoleID
HAVING (UserInRole.Use rRoleID = '1')


Thanks for your help.

James

One way to go is to use the union of the two selects:

SELECT    COUNT(Employees.EmployeeID) AS Count_EmployeeID, Branches.Branches, UserInRole.UserRoleID
FROM      dbo.Employees, dbo.Branches, dbo.UserInRole 
WHERE     dbo.Employees.BranchID = dbo.Branches.BranchID
  AND     dbo.Employees.UserRoleID = dbo.UserInRole.UserRoleID
[B]  AND     Branches.BranchID BETWEEN 1 AND 11[/B]
GROUP BY Branches.Branches, UserInRole.UserRoleID
HAVING  (UserInRole.UserRoleID = '1')

[B]UNION[/B]

SELECT    COUNT(Employees.EmployeeID) AS Count_EmployeeID, 12 AS Branches, UserInRole.UserRoleID
FROM      dbo.Employees, dbo.Branches, dbo.UserInRole 
WHERE     dbo.Employees.BranchID = dbo.Branches.BranchID
  AND     dbo.Employees.UserRoleID = dbo.UserInRole.UserRoleID
[B]  AND     Branches.BranchID BETWEEN 12 AND 14[/B]
GROUP UserInRole.UserRoleID
HAVING  (UserInRole.UserRoleID = '1')

[JRBower]

One way to go is to use the union of the two selects:

SELECT    COUNT(Employees.EmployeeID) AS Count_EmployeeID, Branches.Branches, UserInRole.UserRoleID
FROM      dbo.Employees, dbo.Branches, dbo.UserInRole 
WHERE     dbo.Employees.BranchID = dbo.Branches.BranchID
  AND     dbo.Employees.UserRoleID = dbo.UserInRole.UserRoleID
[B]  AND     Branches.BranchID BETWEEN 1 AND 11[/B]
GROUP BY Branches.Branches, UserInRole.UserRoleID
HAVING  (UserInRole.UserRoleID = '1')

[B]UNION[/B]

SELECT    COUNT(Employees.EmployeeID) AS Count_EmployeeID, 12 AS Branches, UserInRole.UserRoleID
FROM      dbo.Employees, dbo.Branches, dbo.UserInRole 
WHERE     dbo.Employees.BranchID = dbo.Branches.BranchID
  AND     dbo.Employees.UserRoleID = dbo.UserInRole.UserRoleID
[B]  AND     Branches.BranchID BETWEEN 12 AND 14[/B]
GROUP UserInRole.UserRoleID
HAVING  (UserInRole.UserRoleID = '1')

Thanks for your help. I tried to execute the query but I get an error related to the UNION statement. If I run the select statements separately they work fine.

I made a couple small corrections.

SELECT    COUNT(Employees.EmployeeID) AS Count_EmployeeID, 12 AS [B]BranchID[/B], UserInRole.UserRoleID
FROM      dbo.Employees, dbo.Branches, dbo.UserInRole 
WHERE     dbo.Employees.BranchID = dbo.Branches.BranchID
  AND     dbo.Employees.UserRoleID = dbo.UserInRole.UserRoleID
  AND     dbo.Branches.BranchID BETWEEN 12 AND 14
GROUP [B]BY[/B]  UserInRole.UserRoleID
HAVING  (UserInRole.UserRoleID = '1')

12 as BranchID works but I need to return a Branch name.

Is it possible to use the branch names instead of the ID's?
For example,
San Diego as Branches and dbo.Branches.Br anches BETWEEN San Diego AND San Francisco?

Maybe

dbo.Branches.Branches IN ('San Diego','Los Angeles','San Francisco')

Is there another way without the UNION?

Thanks

[azimmer]

Thanks for your help. I tried to execute the query but I get an error related to the UNION statement. If I run the select statements separately they work fine.

I made a couple small corrections.

SELECT    COUNT(Employees.EmployeeID) AS Count_EmployeeID, 12 AS [B]BranchID[/B], UserInRole.UserRoleID
FROM      dbo.Employees, dbo.Branches, dbo.UserInRole 
WHERE     dbo.Employees.BranchID = dbo.Branches.BranchID
  AND     dbo.Employees.UserRoleID = dbo.UserInRole.UserRoleID
  AND     dbo.Branches.BranchID BETWEEN 12 AND 14
GROUP [B]BY[/B]  UserInRole.UserRoleID
HAVING  (UserInRole.UserRoleID = '1')

12 as BranchID works but I need to return a Branch name.

Is it possible to use the branch names instead of the ID's?
For example,
San Diego as Branches and dbo.Branches.Br anches BETWEEN San Diego AND San Francisco?

Maybe

dbo.Branches.Branches IN ('San Diego','Los Angeles','San Francisco')

Is there another way without the UNION?

Thanks

1. Thanks for the "BY" correction, I erased it in error.

2. Without having table defs I didn't know if Braches was ID or name. I believe the correct form is e.g.

SELECT    COUNT(Employees.EmployeeID) AS Count_EmployeeID, 'San Diego' AS [B]Branches[/B], UserInRole.UserRoleID

3. "BETWEEN" is not really useful in this case with branch names (as it would order them alphabetically) , "IN", though, should work fine.

4. I believe that the UNION one works fine. (Maybe because it was my idea :-D) There are other ways, though. One is to have two result columns first (by using two CASEs in the SELECT statement): one for the individual branches and one (summed) for the group. Then you can merge the two in an outer SELECT (by adding them grouped by IDs, which again need to be CASEd in the inner SELECT). I think this is much more complicated and not nearly as clear to read.

[JRBower]

1. Thanks for the "BY" correction, I erased it in error.

2. Without having table defs I didn't know if Braches was ID or name. I believe the correct form is e.g.

SELECT    COUNT(Employees.EmployeeID) AS Count_EmployeeID, 'San Diego' AS [B]Branches[/B], UserInRole.UserRoleID

3. "BETWEEN" is not really useful in this case with branch names (as it would order them alphabetically) , "IN", though, should work fine.

4. I believe that the UNION one works fine. (Maybe because it was my idea :-D) There are other ways, though. One is to have two result columns first (by using two CASEs in the SELECT statement): one for the individual branches and one (summed) for the group. Then you can merge the two in an outer SELECT (by adding them grouped by IDs, which again need to be CASEd in the inner SELECT). I think this is much more complicated and not nearly as clear to read.

Yes, indeed it does work fine! :) I realized that what was causing the error before was in the first select we had Branches.Branch es but in the second select it was 12 as BranchID. Of course your original suggestion was correct in its scripting (without the table defs) however I needed to change 12 AS Branches (your script) to 12 AS BranchID. Now that I have 'San Diego' AS Branches corresponding to Branches.Branch es the UNION works like a charm and the planets are once again properly aligned.

Thank you azimmer!