Sum up columns with the same ID/Job Number

Question

Say I've got a table like below, how do I sum up just the Weekly column by job number and work center.

+-----------+------------+---------------+--------+-----------------
| Job       |  Work_Date | Work_Center   |   Budget |     Weekly   |
+-----------+------------+---------------+--------+------+----------
|      5666 | 2014-02-23 | SURFACE       |     155  |      5       | 
|      5666 | 2014-02-23 | SURFACE       |     155  |      3       |      
|      5666 | 2014-02-23 | DESIGN        |     200  |      6       |                   
+-----------+------------+---------------+----------+--------------+

Turn it into:

+-----------+------------+---------------+--------+-----------------
| Job       |  Work_Date | Work_Center   |   Budget |     Weekly   |
+-----------+------------+---------------+--------+------+----------
|      5666 | 2014-02-23 | SURFACE       |     155  |      8       |     
|      5666 | 2014-02-23 | DESIGN        |     200  |      6       |                   
+-----------+------------+---------------+----------+--------------+

EDIT

Okay Weekly works perfectly! However, an issue I've come across is when getting the sum of budget. Generally, for each Work_Center, the budget stays the same for that specific Job AFAIK. However, there is one missing piece. A number is being added to budget and therefore, using Job 5666's budget as an example, in my Crystal Report its giving me 172 instead of 155. So I did some further digging and turns out it doesn't add duplicate Budgets like in the first table, but it'll add in a value if its not a duplicate (from my understanding).

So I found this row.

+-----------+------------+---------------+--------+-----------------
| Job       |  Work_Date | Work_Center   |   Budget |     Weekly   |
+-----------+------------+---------------+--------+------+----------
|    5666-8 | NULL       | SURFACE       |     17   |      0       |                      
+-----------+------------+---------------+----------+--------------+

Now if I want to add this value to the budget its not working when I try the SQL answers provided below. I've tried trimming the Job # so it just says 5666 so it was see a match in Job #s and it would add the the budget together but that hasn't worked either.

I hope my explanation is a bit clearer.

Do you want to NOT sum the budget column then? I assume that's why you're not just using a standard `GROUP BY`? — chucknelson, Feb 28 '14 at 18:07
@chucknelson I don't want to because the budget doesn't change every week. So if the budget is the same that week for both entries then it shouldn't be summed. — MikeOscarEcho, Feb 28 '14 at 18:15

score 2 · Answer 1 · answered Feb 28 '14 at 18:07

2

Just group the results and sum the Weekly column:

SELECT Job, Work_Date, Work_Center, Budget, SUM(Weekly)
FROM your_table
GROUP BY Work_Center, Job;

answered Feb 28 '14 at 18:07

jterry

6,209
2
30
36

Okay it worked but only if I include Work_Date in the GROUP BY. Error I'm getting: Column 'view_surfacing_report.Work_Date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. – MikeOscarEcho Feb 28 '14 at 18:14

score 2 · Answer 2 · answered Feb 28 '14 at 18:08

2

SELECT Job,  Work_Date, Work_Center,  Budget, SUM( Weekly) FROM your_table GROUP BY Work_Center, Job

answered Feb 28 '14 at 18:08

Avt

16,927
4
52
72

score 2 · Answer 3 · edited May 23 '17 at 11:57

Like Rich Hatch has mentioned, Work_Date cannot be included in the query, because the database would not know which Work_Date to return (theoretically there could be different Work Dates for the same Job and Work Center). Alternatively, you could 1) GROUP BY the Work Date column as well or 2)select MAX(Work Date) which would be the same as Work Date. So, you can write

SELECT Job, Max(Work_Date) as Work_Date, Work_Center, MAX(Budget) as Budget, SUM(Weekly)
FROM your_table
GROUP BY Work_Center, Job;

Related Question on SO

EDIT

In response to your edit, I suggest the following modified queries. You have the option to add only distinct Budget values.

SQL Server

SELECT LEFT(Job, CHARINDEX(Job, "-")-1) as Job, Max(Work_Date) as Work_Date, Work_Center, SUM(DISTINCT Budget) as Budget, SUM(Weekly) as Weekly
FROM your_table
GROUP BY Work_Center, LEFT(Job, CHARINDEX(Job, "-")-1);

Oracle

SELECT SUBSTR(Job, 1, INSTR(Job, "-")-1) as Job, Max(Work_Date) as Work_Date, Work_Center, SUM(DISTINCT Budget) as Budget, SUM(Weekly) as Weekly
FROM your_table
GROUP BY Work_Center, SUBSTR(Job, 1, INSTR(Job, "-")-1);

score 1 · Answer 4 · answered Feb 28 '14 at 18:16

The other answers are missing the budget part.

SELECT Job, Work_Date, Work_Center, avg(Budget), SUM(Weekly)
FROM your_table
GROUP BY Work_Center, Job;

NOTE: if you're working in SQL_STRICT mode, this query will fail because work_date is not in your group by. With that in mind, because Work_Date can be different for each order, it's probably best to leave it out of the query as it is unreliable as to which date it will return.

score 1 · Answer 5 · answered Feb 28 '14 at 18:19

1

If you do only want it grouped by Job and Work_Center the Work_Date seems irrelevant and from your comment it seems you are not concerned with Budget.

You want something closer to this:

SELECT Job, Work_Center, SUM(Weekly)
FROM your_table
GROUP BY Work_Center, Job

answered Feb 28 '14 at 18:19

AhDev

486
11
16

Well I should be concerned with budget. I've just noticed that Budget does have a value added to it in our Crystal Report. I'm not sure how to mimic it in my table. For instance, if the budget is 155, the output for 5666 in crystal is 172, and thats cause its adding 17 to 155. The 17 is coming from a row just like the above except this field doesn't have a Work_Date value (NULL). Not sure how to go about getting that value to add onto the Budget. – MikeOscarEcho Feb 28 '14 at 18:46
Do you want the SUM of the Budget? If so you would just add SUM(Budget) to the columns you are returning. Since SUM is an aggregate you would not have to include them in your group by. I would be happy to help you get what you are looking for; I just not sure I quite understand. – AhDev Feb 28 '14 at 19:45
Yea I understand my explanation doesn't help. I've edited the post above to show you what I mean. I can see how adding SUM would add the budgets but that'll give me a massive number because there are many duplicates of the same budget (155) for a job. Look at my post and I think you'll get an idea of what I mean. – MikeOscarEcho Feb 28 '14 at 20:05
SELECT Job, Work_Date,Work_Center,Budget, SUM(Weekly) FROM your_table GROUP BY Job, Work_Date ,Work_Center,Budget This will separate the null date and group on the budget amount. I think that this is closer to what you are looking for. – AhDev Feb 28 '14 at 21:51

Sum up columns with the same ID/Job Number

5 Answers5