473,324 Members | 2,179 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Need Help to do a GROUP SUM

JRBower
14
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_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
GROUP BY Branches.Branches, UserInRole.UserRoleID
HAVING (UserInRole.UserRoleID = '1')


Thanks for your help.

James
Sep 5 '07 #1
4 1297
azimmer
200 Expert 100+
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_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
GROUP BY Branches.Branches, UserInRole.UserRoleID
HAVING (UserInRole.UserRoleID = '1')


Thanks for your help.

James
One way to go is to use the union of the two selects:
Expand|Select|Wrap|Line Numbers
  1. SELECT    COUNT(Employees.EmployeeID) AS Count_EmployeeID, Branches.Branches, UserInRole.UserRoleID
  2. FROM      dbo.Employees, dbo.Branches, dbo.UserInRole 
  3. WHERE     dbo.Employees.BranchID = dbo.Branches.BranchID
  4.   AND     dbo.Employees.UserRoleID = dbo.UserInRole.UserRoleID
  5.   AND     Branches.BranchID BETWEEN 1 AND 11
  6. GROUP BY Branches.Branches, UserInRole.UserRoleID
  7. HAVING  (UserInRole.UserRoleID = '1')
  8.  
  9. UNION
  10.  
  11. SELECT    COUNT(Employees.EmployeeID) AS Count_EmployeeID, 12 AS Branches, UserInRole.UserRoleID
  12. FROM      dbo.Employees, dbo.Branches, dbo.UserInRole 
  13. WHERE     dbo.Employees.BranchID = dbo.Branches.BranchID
  14.   AND     dbo.Employees.UserRoleID = dbo.UserInRole.UserRoleID
  15.   AND     Branches.BranchID BETWEEN 12 AND 14
  16. GROUP UserInRole.UserRoleID
  17. HAVING  (UserInRole.UserRoleID = '1')
  18.  
Sep 5 '07 #2
JRBower
14
One way to go is to use the union of the two selects:
Expand|Select|Wrap|Line Numbers
  1. SELECT    COUNT(Employees.EmployeeID) AS Count_EmployeeID, Branches.Branches, UserInRole.UserRoleID
  2. FROM      dbo.Employees, dbo.Branches, dbo.UserInRole 
  3. WHERE     dbo.Employees.BranchID = dbo.Branches.BranchID
  4.   AND     dbo.Employees.UserRoleID = dbo.UserInRole.UserRoleID
  5.   AND     Branches.BranchID BETWEEN 1 AND 11
  6. GROUP BY Branches.Branches, UserInRole.UserRoleID
  7. HAVING  (UserInRole.UserRoleID = '1')
  8.  
  9. UNION
  10.  
  11. SELECT    COUNT(Employees.EmployeeID) AS Count_EmployeeID, 12 AS Branches, UserInRole.UserRoleID
  12. FROM      dbo.Employees, dbo.Branches, dbo.UserInRole 
  13. WHERE     dbo.Employees.BranchID = dbo.Branches.BranchID
  14.   AND     dbo.Employees.UserRoleID = dbo.UserInRole.UserRoleID
  15.   AND     Branches.BranchID BETWEEN 12 AND 14
  16. GROUP UserInRole.UserRoleID
  17. HAVING  (UserInRole.UserRoleID = '1')
  18.  
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT    COUNT(Employees.EmployeeID) AS Count_EmployeeID, 12 AS BranchID, UserInRole.UserRoleID
  2. FROM      dbo.Employees, dbo.Branches, dbo.UserInRole 
  3. WHERE     dbo.Employees.BranchID = dbo.Branches.BranchID
  4.   AND     dbo.Employees.UserRoleID = dbo.UserInRole.UserRoleID
  5.   AND     dbo.Branches.BranchID BETWEEN 12 AND 14
  6. GROUP BY  UserInRole.UserRoleID
  7. 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.Branches BETWEEN San Diego AND San Francisco?

Maybe
Expand|Select|Wrap|Line Numbers
  1. dbo.Branches.Branches IN ('San Diego','Los Angeles','San Francisco')
Is there another way without the UNION?

Thanks
Sep 5 '07 #3
azimmer
200 Expert 100+
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT    COUNT(Employees.EmployeeID) AS Count_EmployeeID, 12 AS BranchID, UserInRole.UserRoleID
  2. FROM      dbo.Employees, dbo.Branches, dbo.UserInRole 
  3. WHERE     dbo.Employees.BranchID = dbo.Branches.BranchID
  4.   AND     dbo.Employees.UserRoleID = dbo.UserInRole.UserRoleID
  5.   AND     dbo.Branches.BranchID BETWEEN 12 AND 14
  6. GROUP BY  UserInRole.UserRoleID
  7. 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.Branches BETWEEN San Diego AND San Francisco?

Maybe
Expand|Select|Wrap|Line Numbers
  1. 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.
Expand|Select|Wrap|Line Numbers
  1. SELECT    COUNT(Employees.EmployeeID) AS Count_EmployeeID, 'San Diego' AS Branches, UserInRole.UserRoleID
  2.  
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.
Sep 5 '07 #4
JRBower
14
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT    COUNT(Employees.EmployeeID) AS Count_EmployeeID, 'San Diego' AS Branches, UserInRole.UserRoleID
  2.  
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.Branches 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.Branches the UNION works like a charm and the planets are once again properly aligned.

Thank you azimmer!
Sep 5 '07 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

9
by: netpurpose | last post by:
I need to extract data from this table to find the lowest prices of each product as of today. The product will be listed/grouped by the name only, discarding the product code - I use...
7
by: Shyguy | last post by:
I have an option group with about 30 options. I would like to have the Option Labels get their text from a table. Is this possible, and if so how? Also, is it possible to embad a font into a...
4
by: Brian Coy | last post by:
I have a database that records a part no, a scrap reason, the cost of scrapping that item, and the date. I need to porduce a report that will show up to a weeks worth of data at a time, but I need...
7
by: ChadDiesel | last post by:
Hello everyone, I'm having a problem with Access that I need some help with. The short version is, I want to print a list of parts and part quantities that belong to a certain part group---One...
10
by: Tom | last post by:
I am looking for some ideas for how to design the layout of the form for data entry and to display the data for the following situation: There are many sales associates. A sales associate can work...
0
by: ward | last post by:
Greetings. Ok, I admit it, I bit off a bit more than I can chew. I need to complete this "Generate Report" page for my employer and I'm a little over my head. I could use some additional...
0
by: U S Contractors Offering Service A Non-profit | last post by:
Brilliant technology helping those most in need Inbox Reply U S Contractors Offering Service A Non-profit show details 10:37 pm (1 hour ago) Brilliant technology helping those most in need ...
3
by: sunbeam | last post by:
Short Description of the Project: we developed a e-learning system for our students. each student has a unique username/password to view the modules he/she should view and nothing more. since we...
0
by: Chuck36963 | last post by:
Hi all, I've been working on a listing problem and I can't figure out how to work it out. I have looked far and wide on the web to find answers, but I'd like other peoples input on my project in...
2
by: speavey | last post by:
When I run this query, I get an ORDER BY error "Incorrect syntax near the keyword 'ORDER'. I've bolded it below. If I take the ORDER by out then it works correctly, but I need the ORDER BY...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.