CODEWITHSUNDEEP
mysqlsql
Ondřej Douša
Ondřej Douša

Reputation: 73

SELECT and SUM from multiple tables

I need single SQL query for SELECT list of jobs including SUM() of sme specific detail type.

I have database with transport data. Tables looks like that:

job:
idjob | customer
1     | 45
2     | 38
3     | 15

job-detail:
iddet | idjob | type  | value
1     | 1     | range | 100
2     | 1     | range | 85
3     | 1     | range | 12
4     | 1     | price | 64
4     | 1     | price | 5
5     | 1     | note  | Some text here
6     | 2     | range | 150
7     | 2     | price | 32
8     | 2     | note  | Some text here
9     | 2     | range | 35

I need this output:

idjob | customer | total_range | total_price
1     | 45       | 197         | 69
2     | 38       | 185         | 32
3     | 15       | 0           | 0

Upvotes: 1

Views: 209

Answers (2)

Saeid Amini
Saeid Amini

Reputation: 1307

SELECT DISTINCT J.idjob, J.customer, 
(SELECT SUM(CONVERT(INT, [VALUE])) FROM [dbo].[job-detail] WHERE TYPE = 'range' AND J.idjob = idjob) AS total_range,
(SELECT SUM(CONVERT(INT, [VALUE])) FROM [dbo].[job-detail] WHERE TYPE = 'price' AND J.idjob = idjob) AS total_price
 FROM [dbo].[job] j
LEFT JOIN [dbo].[job-detail] JD ON J.idjob = JD.idjob

Upvotes: 0

Fahmi
Fahmi

Reputation: 37493

you can use left join with conditional aggregation 

select a.idjob,customer,
       sum(case when type='range' then value end) as total_range,
       sum(case when type='price' then value end) as total_price
from job a 
left join job-detail b on a.idjob=b.idjob 
group by a.idjob,customer

Upvotes: 3

Related Questions