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

  • 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

    Monday, November 5, 2018 5:23 PM

Answers

  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 6, 2018 2:09 AM

All replies

  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 6, 2018 2:09 AM
  • 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

    Tuesday, November 6, 2018 4:42 AM