How to use sum of values in SQL joins

Question

Please help me to find the sum of Service_times from linked tables.

CREATE TABLE incidents (incident_id int,date_logged datetime,usr_id int,dept_id int,item_id int, cust_id int)  

insert into incidents values 
('1001',    '8/20/2016',    '190',  '3',    '800',  '10'),
('1002',    '8/21/2016',    '111',  '4',    '810',  '12'),
('1003',    '8/22/2016',    '190',  '3',    '800',  '10'),
('1004',    '8/23/2016',    '111',  '4',    '822',  '12')


Create TABLE actions ( act_id int, act_type varchar(50) ,   incident_id int,    usr_id   int ,date_actioned datetime,   service_time money)

Insert into actions VALUES
('1',   'TRAVEL',   1001,   190,    8/20/2016,  20),
('2',   'ASSIGN',   1001,   2,  8/21/2016,  1),
('3',   'TRAVEL',   1001,   190 ,8/22/2016, 10),
('4',   'REMOTE',   1001,   190,    8/23/2016,  30),
('5',   'TRAVEL',   1002,   111,    8/21/2016,  40),
('6',   'ASSIGN',   1002,   2,  8/22/2016   ,1),
('7',   'REMOTE',   1002,   111,    8/23/2016,  30),
('8',   'TRAVEL',   1002,   111,    8/24/2016,  60),
('9',   'TRAVEL',   1003,   190,    8/22/2016,  45),
('10',  'ASSIGN',   1003,   2,  8/23/2016   ,1),
('11',  'REMOTE',   1003,   190 ,8/23/2016  ,10),
('12',  'REMOTE',   1003,   190 ,8/23/2016  ,20),
('13',  'ASSIGN',   1004,   2   ,8/23/2016  ,1),
('14',  'TRAVEL',   1004,   111,    8/23/2016,  20),
('15',  'TRAVEL',   1004,   111,    8/23/2016,  20),
('16',  'REMOTE',   1004,   111,    8/23/2016,  20)


CREATE TABLE inc_data (incident_id int,Rep1 char(1), Rep2 char(1),  Rep3 char(1),   Res1 char(1),   Res2 char(1),   Res3 char(1))   

insert into inc_data values
(1001,  'y',    'y',    'y',    'y',    'y',    'n'),
(1002,  'n',    'n',    'n',    'n',    'n',    'n'),
(1003,  'y',    'y',    'n',    'n',    'n',    'n'),
(1004,  'y',    'y',    'y',    'y' ,   'y',    'y')

My Query:

SELECT i.usr_id,
       COUNT(CASE WHEN inc_data.Rep3 = 'y' THEN 1 END) AS RespBreach,
       COUNT(CASE WHEN inc_data.Res3 = 'y' THEN 1 END) AS ResBreach,
       COUNT(i.incident_id)as CallCount,
       (SELECT     ISNULL(SUM(A.service_time), 0) AS Expr1
         FROM   actions AS  WHERE  A.act_type= 'TRAVEL')) AS Travel_Time,
       (SELECT     ISNULL(SUM(A.service_time), 0) AS Expr1
         FROM   actions AS  WHERE  A.act_type= 'REMOTE')) AS Remote_Time      
FROM incidents as i
INNER JOIN inc_data ON i.incident_id= inc_data.incident_id
INNER JOIN actions act1 on i.incident_id=act1.incident_id 
GROUP BY i.usr_id

Expected Result:

   +--------+-----------+-------------+-------------+-----------+-----------+--+--+--+
| usr_id | Callcount | Travel_Time | Remote_Time | RepBreach | ResBreach |  |  |  |
+--------+-----------+-------------+-------------+-----------+-----------+--+--+--+
|    190 |         2 |          75 |          60 |         1 |         0 |  |  |  |
|    111 |         2 |         140 |          50 |         1 |         1 |  |  |  |
+--------+-----------+-------------+-------------+-----------+-----------+--+--+--+

plus 1 for sample data,could you also explain little bit about logic — TheGameiswar
– TheGameiswar, Commented Aug 24, 2016 at 14:36

Vamsi Prabhala · Accepted Answer · 2016-08-24 14:43:29Z

1

No need of correlated sub-queries for calculating the remote and travel times. As the actions table is joined you can simply use conditional aggregation.

Also the counts should be for distinct incident_id's as the relationship is one-many.

SELECT i.usr_id,
       COUNT(DISTINCT CASE WHEN inc_data.Rep3 = 'y' THEN 1 END) AS RespBreach,
       COUNT(DISTINCT CASE WHEN inc_data.Res3 = 'y' THEN 1 END) AS ResBreach,
       COUNT(DISTINCT i.incident_id) as CallCount,
       SUM(CASE WHEN act1.act_type= 'TRAVEL' THEN ISNULL(act1.service_time, 0) ELSE 0 END) AS Travel_Time,
       SUM(CASE WHEN act1.act_type= 'REMOTE' THEN ISNULL(act1.service_time, 0) ELSE 0 END) AS Remote_Time      
FROM incidents as i
INNER JOIN inc_data ON i.incident_id= inc_data.incident_id
INNER JOIN actions act1 on i.incident_id=act1.incident_id 
GROUP BY i.usr_id

Sample Demo

answered Aug 24, 2016 at 14:43

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sam Bin Ham Over a year ago

:Thanks for your query, if i have one more action (REMOTE) in actions for incident=1001 by user 111 . it is adding to user 190. How i can filter this. Appreciated your help. edited Demo here : rextester.com/WLEMT12994

Collectives™ on Stack Overflow

How to use sum of values in SQL joins

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related