CODEWITHSUNDEEP
sql
PipRon7
PipRon7

Reputation: 87

Column Sum in SQL

I have the below query:

select
 a.jobid
,a.jobnavn
,a.jobstart
,a.ExpTypeDescr
,b.Quote
,b.Expense
,b.Invoiced
,b.Actual

from job a
             INNER JOIN JobAdjustmentDetails AS b ON a.jobid = b.jobid

where a.jobid = '704289'

That generates a table like below:

enter image description here

I would like to sum the columns so that i get one line for each of the types in the column 'ExpTypeDescr' so it looks like below?

enter image description here

Can anyone advise the simplest way to do this?

I was trying sum(distinct) on each column holding a value but it wasn't the result I was hoping for.

Thanks

Upvotes: 0

Views: 48

Answers (2)

sam
sam

Reputation: 1985

Please use group by:

select
 a.jobid
,a.jobnavn
,a.jobstart
,a.ExpTypeDescr
, sum(b.Quote)
,sum(b.Expense)
,sum(b.Invoiced)
,sum(b.Actual)

from job a
    INNER JOIN JobAdjustmentDetails AS b ON a.jobid = b.jobid

where a.jobid = '704289'  
group by a.jobid, a.jobnavn, a.jobstart, a.ExpTypeDescr

Upvotes: 0

kjmerf
kjmerf

Reputation: 4345

Use a group by:

SELECT a.jobid
    ,a.jobnavn
    ,a.jobstart
    ,a.ExpTypeDescr
    ,sum(b.Quote) AS Quote
    ,sum(b.Expense) AS Expense
    ,sum(b.Invoiced) AS Invoiced
    ,sum(b.Actual) AS Actual
FROM job a
INNER JOIN JobAdjustmentDetails AS b ON a.jobid = b.jobid
WHERE a.jobid = '704289'
GROUP BY a.jobid
    ,a.jobnavn
    ,a.jobstart
    ,a.ExpTypeDescr

Upvotes: 2

Related Questions