CODEWITHSUNDEEP
mysqlsql
MikeOscarEcho
MikeOscarEcho

Reputation: 548

Sum up columns with the same ID/Job Number

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.

Upvotes: 0

Views: 127

Answers (5)

Joseph B
Joseph B

Reputation: 5669

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);

Upvotes: 2

AhDev
AhDev

Reputation: 486

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

Upvotes: 1

Rich Hatch
Rich Hatch

Reputation: 86

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.

Upvotes: 1

Avt
Avt

Reputation: 17053

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

Upvotes: 2

jterry
jterry

Reputation: 6269

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;

Upvotes: 2

Related Questions