CODEWITHSUNDEEP
sqlsql-server
user6647541
user6647541

Reputation:

How to add multiple rows into one row with total

How to add multiple rows into one row with total? I am trying to add two rows of data and output the data into one row for one particular job. I have cut output down to one job but I will be putting multiple jobs in the output once I figure out how to get two rows to combine to one. Each job has two employees so I will have to add both employees hours together for a total hours worked per job.

SELECT
     TJ.intJobID
    ,TJ.strJobDescription
    ,TJS.strJobStatus
    ,SUM(TJE.intHoursWorked) AS intTotalHoursWorked

FROM
     TJobs          AS TJ
    ,TJobStatus     AS TJS
    ,TJobEmployees  AS TJE
    ,TEmployees     AS TE
WHERE
    TJ.intJobID         =       TJE.intJobID
AND TJ.intJobStatusID   =       TJS.intJobStatusID
AND TE.intEmployeeID    =       TJE.intEmployeeID
AND TJ.intJobID = 1
GROUP BY
    TJ.intJobID
   ,TJE.intEmployeeID
   ,TJ.strJobDescription
   ,TJS.strJobStatus
   ,TJE.intHoursWorked


 /*

     Output I'm Getting

    intJobID    strJobStatus    intTotalHoursWorked
       1          Complete              50
       1          Complete              42

     Wanted Output

    intJobID    strJobStatus    intTotalHoursWorked
       1          Complete              92

 */

Upvotes: 0

Views: 159

Answers (2)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48207

SELECT
     TJ.intJobID
    ,TJS.strJobStatus
    ,SUM(TJE.intHoursWorked) AS intTotalHoursWorked

...
...
GROUP BY
    TJ.intJobID
   ,TJS.strJobStatus

I double check your query and you have too many tables. Because you dont really need TEmployees 

SELECT
     TJ.intJobID
    ,TJ.strJobDescription
    ,TJS.strJobStatus
    ,SUM(TJE.intHoursWorked) AS intTotalHoursWorked

FROM TJobs          AS TJ
JOIN TJobStatus     AS TJS
  ON TJ.intJobStatusID =  TJS.intJobStatusID
JOIN TJobEmployees  AS TJE
  ON TJ.intJobID       =  TJE.intJobID
GROUP BY
    TJ.intJobID
   ,TJS.strJobStatus

Upvotes: 1

TheGameiswar
TheGameiswar

Reputation: 28940

One option would be using CTE..since we don't know your data and what column in group by caused mutiple columns

;with cte
as
(
SELECT
     TJ.intJobID
    ,TJ.strJobDescription
    ,TJS.strJobStatus
    ,SUM(TJE.intHoursWorked) AS intTotalHoursWorked

FROM
     TJobs          AS TJ
    ,TJobStatus     AS TJS
    ,TJobEmployees  AS TJE
    ,TEmployees     AS TE
WHERE
    TJ.intJobID         =       TJE.intJobID
AND TJ.intJobStatusID   =       TJS.intJobStatusID
AND TE.intEmployeeID    =       TJE.intEmployeeID
AND TJ.intJobID = 1
GROUP BY
    TJ.intJobID
   ,TJE.intEmployeeID
   ,TJ.strJobDescription
   ,TJS.strJobStatus
   ,TJE.intHoursWorked
)
select 
intJobID,
max(strJobStatus) as 'strJobStatus',
sum(intTotalHoursWorked) as 'intTotalHoursWorked'
from cte

Upvotes: 0

Related Questions