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
Solved

Sum of multiple columns

Posted on 2014-03-21
3
321 Views
Last Modified: 2014-03-22
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!
0
Comment
Question by:MrBeedge
  • 2
3 Comments
 
LVL 12

Expert Comment

by:Tony303
ID: 39946798
How about getting the 3 Techs and Hours separated out then brought together in a UNION?

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

Open in new window

0
 

Author Comment

by:MrBeedge
ID: 39947066
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,
0
 
LVL 12

Accepted Solution

by:
Tony303 earned 500 total points
ID: 39947070
Ok,

Let's try this..

SELECT Tech, SUM(Hours)  as TotalHours
FROM
(
SELECT Tech1 as Tech, ISNULL(Hours1,0) as Hours
FROM Table
UNION ALL
SELECT Tech2 as Tech, ISNULL(Hours2,0)  as Hours
FROM Table
UNION ALL
SELECT Tech3 as Tech, ISNULL(Hours3,0) as Hours
FROM Table
) a
GROUP BY Tech 

Open in new window

0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL replication over high latency link 10 58
SQL query and VBA 5 45
SSRS: Why is Visual Studio stripping these properties? 2 21
job schedule 8 18
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question