Solved

Sum of multiple columns

Posted on 2014-03-21

Microsoft SQL Server

1 solution

324 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!

Comment

Question by:MrBeedge

[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

Join The Community

3 Comments

LVL 12

Expert Comment

by:Tony303

ID: 399467982014-03-21

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

Author Comment

by:MrBeedge

ID: 399470662014-03-22

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,

LVL 12

Accepted Solution

by:Tony303

Tony303 earned 500 total points

ID: 399470702014-03-22

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

Featured Post

Question has a verified solution.

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

Suggested Solutions

Title	# Comments	Views	Activity
Format Date fields	11	65	2017-05-09
Sub form showing data is being saved but cannot be displayed..	31	69	2017-05-11
VM SQL server license.	1	67	2017-05-15
Need SQL Query improved for multiple column joins	2	15	3d

Installing SQL Server vNext on Linux: Part 1 (Prerequisites)

Article by: Yashwant

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.

How to dynamically return a table structure using a Stored Procedure

Article by: Ryan Chong

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…

Loading Excel or CSV Data into SQL Server

Video by: Steve

Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

Microsoft SQL Server

Dealing with Dates: Data Types and String Conversions (SQL Server)

Video by: Steve

Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.