Select Sum of a value form multiple records share the same value from another record

Question

User1700504264 posted

Hello

I am using this statement to select records from two tables

SELECT r.ID as Code, 
					r.intervalStart as RecordStart, 
					r.intervalEnd as RecordEnd, 
					r.expectedRecord as Target, 
					r.achievedRecord as Record, 
					r.recordDate as RecordDate,
					r.evidence as evidence,
					r.stumblingReason as stumblingreason,
					a.title as ActivityName,  
					a.[status] as ActivityStatus,
					ActivityStatusColor=(CASE a.[status] WHEN 0 THEN 'LightSlateGray' WHEN 1 THEN 'Dodgerblue' WHEN 2 THEN 'MediumSpringGreen'  WHEN 3 THEN 'Tomato' END ), 
					a.kpiValue as kpiValue,
					0 as achieved
	FROM Record r
	INNER JOIN Activity a ON r.Activity=a.ID
	

I need to select the sum value of records from <g class="gr_ gr_260 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins doubleReplace replaceWithoutSep" id="260" data-gr-id="260">table</g> (Record) where they share the same activity ID, 

something like SUM (r.achievedRecord) Where r.Activity - Activity.ID

How could I implement this?

Thank you

Answer 1

User-893317190 posted

Hi 0591666959,

It seems you want to get  the aggregate value of the connection data in a inner join.

If so , you could try subquery.

Below is my sample. 

I have two tables , employee and department, one department could have many employees and one employees only have one department.

Employee has reference to department through department_id column.

Below is the employee table.

id      name   salary   department_id
1	tom	4000	1
2	jerry	3500	1
3	helen	3700	1
4	acker	3000	2
5	ank	2900	2
6	ai	4000	3
7	sai	4100	3
8	tam	4200	3
9	kite	4300	3

Department table.

id      department_name
1	Personnel
2	Finance
3	technical

Below is my query. In the subquery , you get the joined department_id , and use the id to query for how many employees in the corresponding department from the employee table.

select e.name,e.salary,d.id department_id ,d.department_name ,
(select count(*) from employee ee where ee.department_id=d.id) as total
 from employee e inner join department d
on e.department_id=d.id   

The result.

name salary department_id department_name    total
 tom	4000	1	Personnel	     3
jerry	3500	1	Personnel	     3
helen	3700	1	Personnel	     3
acker	3000	2	Finance	             2
ank	2900	2	Finance	             2
ai	4000	3	technical	     4
sai	4100	3	technical	     4
tam	4200	3	technical	     4
kite	4300	3	technical	     4

For more information about subquery, please refer to https://docs.microsoft.com/en-us/sql/relational-databases/performance/subqueries?view=sql-server-2017

If it is not your case, please post the structure of your tables and  same data in the two tables.

Best regards,

Ackerly Xu

Answer 2

User1700504264 posted

<g class="gr_ gr_3 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="3" data-gr-id="3">Thenk</g> you Ackerly Xu