SUM for all Jobs

Question

I have a SQL statment that I am trying to pull the sum for all JobTypes, for example, if there are 5 Jobs with the name "Process" they should show up on only one line with the Sum for the GB and KB. How do I accomplish this? Thanks.

so i would show: – jpavlov 10 mins ago
DEC 1, 2012, Data Extrat, sum(60), sum(56) – jpavlov 8 mins ago
DEC 1, 2012, Process, sum(60), sum(56)

   SELECT 

Jobs.DateCompleted  AS 'DateCompleted',
JobTypes.Name AS 'JobName',
SUM(Metrics.GB) AS GB, 
SUM(Metrics.KB) AS KB
FROM Metrics 

INNER JOIN
Jobs ON Jobs.JobId = Metrics.JobId 
INNER JOIN

Projects ON Projects.ProjectId = Jobs.ProjectId 

INNER JOIN
JobTypes ON JobTypes.JobTypeId = Jobs.JobTypeId

WHERE Jobs.DateCompleted 
Between '12/01/2012' AND '12/03/2012' 

GROUP BY  
Jobs.DateCompleted, 
JobTypes.Name

2012-12-01 04:28:15.477 Data Extract    0.200   210903.738
2012-12-01 04:40:07.913 Data Extract    0.781   819388.602
2012-12-01 04:48:45.493 Data Extract    5.278   5538731.844
2012-12-01 04:54:55.483 Data Extract    6.927   7265870.682
2012-12-01 14:11:38.357 Export  13.189  13828648.369
2012-12-01 16:57:54.840 Process 2.054   2156185.331

score 1 · Accepted Answer · edited May 23 '17 at 12:03

1

Remove CAST (Jobs.DateCompleted AS VARCHAR(MAX)) From the SELECT as well as GROUP BY.

Assumption: JobTypes.Name and JobTypes.JobTypeId has 1 to 1 mapping.

EDIT

SELECT 
JobTypes.Name AS 'JobName',
SUM(Metrics.GB) AS GB, 
SUM(Metrics.KB) AS KB
FROM Metrics 

INNER JOIN
Jobs ON Jobs.JobId = Metrics.JobId 
INNER JOIN

Projects ON Projects.ProjectId = Jobs.ProjectId 

INNER JOIN
JobTypes ON JobTypes.JobTypeId = Jobs.JobTypeId

WHERE Jobs.DateCompleted 
Between '12/01/2012' AND '12/03/2012' 

GROUP BY  
JobTypes.Name

For the result to get summed up by Name field, you will have to remove the DateCompleted from Select as well as Group By.

EDIT2:

Take a look at this question and modify your sql.

SELECT 
convert(varchar(10), Jobs.DateCompleted, 120) AS DateCompleted
JobTypes.Name AS 'JobName',
SUM(Metrics.GB) AS GB, 
.....
.....
GROUP BY  
convert(varchar(10), Jobs.DateCompleted, 120),
JobTypes.Name

edited May 23 '17 at 12:03

Community

1
1

answered Dec 04 '12 at 14:44

shahkalpesh

33,172
3
63
88

I edited the query, removed the casts but still no luck. Also, JobType.JobId is removed, so It can just be sumed by date and Job Name – jpavlov Dec 04 '12 at 14:51
Do you want to get date in the result without the time part such that the result is datewise details of each job with GB and KB? – shahkalpesh Dec 04 '12 at 15:02
Yes, that is exactly what i am after – jpavlov Dec 04 '12 at 15:03
DEC 1, 2012, Process, sum(60), sum(56) – jpavlov Dec 04 '12 at 15:05
@jpavlov: It is better to modify the question, instead of adding comments. This will help other people to look at your question in one go. Please delete the below 3 comments and add it to the original question. – shahkalpesh Dec 04 '12 at 15:12

SUM for all Jobs

1 Answers1