CODEWITHSUNDEEP
sqlsql-server
Adrian Warness
Adrian Warness

Reputation: 69

Combining rows based on Job ID in SQL Server

New to SQL working with SQL Server. I have a query that pulls all the information we need to perform analysis on a group of clients but based on how the system is set up it currently requires manual clean up in excel before the data becomes useful.

Some Job ID's end with S but when when we do analysis we look at the Job ID as a whole and have to spend a lot of time in excel manually combining the data together.

Example query result

JOB ID   LABOR EXPENSE  DIRECT MATERIAL
10067    400395.29      96614.25
10067S   143668         44148.22
10068    545100.79      127143.64
10068S   147009.89      33506.31

What I would like to do is when the query is run combine the rows with the S in the Job ID into the ones without so we just get one line of data for each job.

Example

Job ID  LABOR EXPENSE   DIRECT MATERIAL
10067   544063.29       140762.47
10068   692110.68       160649.95

Does anyone have any ideas on how I can alter my query to accomplish this?

Upvotes: 3

Views: 107

Answers (2)

Stidgeon
Stidgeon

Reputation: 2723

A variation on a theme that works (if your id values consistent of numbers of consistent digit length) is to use substring:

SELECT SUBSTRING(job_id,1,5) AS job_no, 
SUM(labor_exp) AS tot_lab,
SUM(direct_mat) AS tot_mat
FROM jobs
GROUP BY SUBSTRING(job_id,1,5)

SQLFiddle: http://sqlfiddle.com/#!9/72838/16

Upvotes: 1

Jorge Campos
Jorge Campos

Reputation: 23381

Try this:

select replace( Job_Id, 'S', '' ) as "JOB ID",
       sum (LABOR_EXPENSE) as "LABOR EXPENSE",
       sum (DIRECT_MATERIAL) as "DIRECT MATERIAL"
  from yourTable
group by replace( Job_Id, 'S', '' )
order by replace( Job_Id, 'S', '' )

Remember to change the columns accordingly since it may not be defined with spaces or even as I suggested with underlines.

Upvotes: 3

Related Questions