In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.
Solved
Sum of multiple columns
Posted on 2014-03-21
I have an old database that looks like this:
Job Tech1 Hours1 Tech2 Hours2 Tech3 Hours3
A Bob 1 Sam 2 Joe 3
B Sam 2 Joe 3 Bob 1
C Joe 3 Bob 1 Sam
The people in the database can fulfill different rolls on a job so they are put in different columns.
I need a query that figures out the total for each person. In the above case, this would be:
Tech Total Hours
Bob 3
Sam 6
Joe 9
I have this in an Access database with multiple queries that perform the calculation, but am looking for a way to simplify into 1 query.
Thanks!
Job Tech1 Hours1 Tech2 Hours2 Tech3 Hours3
A Bob 1 Sam 2 Joe 3
B Sam 2 Joe 3 Bob 1
C Joe 3 Bob 1 Sam
The people in the database can fulfill different rolls on a job so they are put in different columns.
I need a query that figures out the total for each person. In the above case, this would be:
Tech Total Hours
Bob 3
Sam 6
Joe 9
I have this in an Access database with multiple queries that perform the calculation, but am looking for a way to simplify into 1 query.
Thanks!
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
2
3 Comments
Active 7 days ago
How about getting the 3 Techs and Hours separated out then brought together in a UNION?
Something like this....
Something like this....
SELECT Tech, SUM(Hours) as TotalHours
FROM
(
SELECT Tech1 as Tech, Hours1 as Hours
FROM Table
UNION
SELECT Tech2 as Tech, Hours2 as Hours
FROM Table
UNION
SELECT Tech3 as Tech, Hours3 as Hours
FROM Table
) a
GROUP BY Tech
Tony -
Thanks for your reply. I think some nulls in the hours (Hours1, Hours2, Hours3) are causing some issues. If I only include the first Hours1 in the query, things total up fine. When I add the Hours2 and Hours 3 using UNION, the Total Hours actually go down. There will be some times in this database where the Tech Name is filled in, but no hours will be entered.
So, this is definitely simplified to what I have in my access database, but doesn't quite work,
Thanks for your reply. I think some nulls in the hours (Hours1, Hours2, Hours3) are causing some issues. If I only include the first Hours1 in the query, things total up fine. When I add the Hours2 and Hours 3 using UNION, the Total Hours actually go down. There will be some times in this database where the Tech Name is filled in, but no hours will be entered.
So, this is definitely simplified to what I have in my access database, but doesn't quite work,
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Why is this different from all of the other step by step guides? Because I make a living as a DBA and not as a writer and I lived through this experience.
Defining the name:
When I talk to people they say different names on this subject stuff l…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses
Course of the Month4 days, 19 hours left to enroll
670 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.