Sum of Rows per a unique id

• raviojo

  SSC Veteran

  Points: 293

  More actions

  January 14, 2017 at 8:23 pm

  #401386

  Hi Friends,

  I have  a table in the following format

  Table A -Sample Data

  Record_Id       Record_text         Record_Answer   Measure_Id

      1                    T                  50                             11
       1                   A                  20                             11
        1                   C                  3                              11
       1                    T                 70                              12
       1                   A                   10                             12
        1                   C                  4                               12  
        3                    T                 80                             15
        3                  A                   40                             15
        3                   C                  6                                15

  I have to use this formula   (T-A)* C  and get the calculation per Record_id  and sum the  calculation per Measure_id
  Thats is if I pass  Record ID  1 the calculation should be : (50-20)*3 + (70-10)*4 =330
  Same if i send Record id 3    then   (80-40)*6 =240

  Whats the best way to handle this calculation ?
  Thank You for the help...

• Jeff Moden

  SSC Guru

  Points: 1003865

  More actions

  January 14, 2017 at 9:06 pm

  #1922778

  The comments in the code below explain how this can be done.  Also, see the first link under "Helpful Links" in my signature line for how to post to get the best help possible as quickly as possible.  I created the data this time because you're new to this forum.
  

  --=============================================================================
--      Create and populate a test table from the provided data.
--      Nothing in this section is a part of the solution. 
--      We're just making test data.
--=============================================================================
--===== If the test table exists, drop it to make reruns in SSMS easier.
     IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL
        DROP TABLE #TestTable
;
--===== Create and populate the test table on-the-fly.
 SELECT d.*
   INTO #TestTable
   FROM (
         SELECT 1,'T',50,11 UNION ALL 
         SELECT 1,'A',20,11 UNION ALL 
         SELECT 1,'C', 3,11 UNION ALL 
         SELECT 1,'T',70,12 UNION ALL 
         SELECT 1,'A',10,12 UNION ALL 
         SELECT 1,'C', 4,12 UNION ALL 
         SELECT 3,'T',80,15 UNION ALL 
         SELECT 3,'A',40,15 UNION ALL 
         SELECT 3,'C', 6,15
        ) d (Record_Id,Record_text,Record_Answer,Measure_Id)
;
--===== Display the content of the test table
 SELECT * FROM #TestTable
;


  Here's one possible solution...
  

  --=============================================================================
  --      This is one possible solution that uses DRY code and Divide'n'Conquer.
  --=============================================================================
  --===== Calulate all of the TAC totals for each Record_ID.
       -- If you really only need 1 Record_ID at a time, add a variable for
       -- which Record_ID to use in a WHERE clause in the CTE.
       -- I commented one out for you.
     WITH ctePivot AS
  ( --=== Pivot the TAC columns to the same row for Record_ID, Measure_ID combos.
   SELECT  Record_ID
          ,T = SUM(CASE WHEN Record_text = 'T' THEN Record_Answer ELSE 0 END)
          ,A = SUM(CASE WHEN Record_text = 'A' THEN Record_Answer ELSE 0 END)
          ,C = SUM(CASE WHEN Record_text = 'C' THEN Record_Answer ELSE 0 END)
  --WHERE Record_ID = @DesiredRecord_ID   FROM #TestTable
    GROUP BY Record_ID, Measure_ID
  ) --=== Calculate the final result.
   SELECT  Record_ID
          ,TAC = SUM((T-A)*C)
     FROM ctePivot
    GROUP BY Record_ID
  ;
  Results:
  
 Record_ID   TAC
----------- -----------
1           330
3           240


  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• raviojo

  SSC Veteran

  Points: 293

  More actions

  January 15, 2017 at 12:13 pm

  #1922811

  Jeff Moden - Saturday, January 14, 2017 9:06 PM

  The comments in the code below explain how this can be done.  Also, see the first link under "Helpful Links" in my signature line for how to post to get the best help possible as quickly as possible.  I created the data this time because you're new to this forum.
  

  --=============================================================================
  --      Create and populate a test table from the provided data.
  --      Nothing in this section is a part of the solution. 
  --      We're just making test data.
  --=============================================================================
  --===== If the test table exists, drop it to make reruns in SSMS easier.
       IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL
          DROP TABLE #TestTable
  ;
  --===== Create and populate the test table on-the-fly.
   SELECT d.*
     INTO #TestTable
     FROM (
           SELECT 1,'T',50,11 UNION ALL 
           SELECT 1,'A',20,11 UNION ALL 
           SELECT 1,'C', 3,11 UNION ALL 
           SELECT 1,'T',70,12 UNION ALL 
           SELECT 1,'A',10,12 UNION ALL 
           SELECT 1,'C', 4,12 UNION ALL 
           SELECT 3,'T',80,15 UNION ALL 
           SELECT 3,'A',40,15 UNION ALL 
           SELECT 3,'C', 6,15
          ) d (Record_Id,Record_text,Record_Answer,Measure_Id)
  ;
  --===== Display the content of the test table
   SELECT * FROM #TestTable
  ;
  

  Here's one possible solution...
  

  --=============================================================================
  --      This is one possible solution that uses DRY code and Divide'n'Conquer.
  --=============================================================================
  --===== Calulate all of the TAC totals for each Record_ID.
       -- If you really only need 1 Record_ID at a time, add a variable for
       -- which Record_ID to use in a WHERE clause in the CTE.
       -- I commented one out for you.
     WITH ctePivot AS
  ( --=== Pivot the TAC columns to the same row for Record_ID, Measure_ID combos.
   SELECT  Record_ID
          ,T = SUM(CASE WHEN Record_text = 'T' THEN Record_Answer ELSE 0 END)
          ,A = SUM(CASE WHEN Record_text = 'A' THEN Record_Answer ELSE 0 END)
          ,C = SUM(CASE WHEN Record_text = 'C' THEN Record_Answer ELSE 0 END)
  --WHERE Record_ID = @DesiredRecord_ID   FROM #TestTable
    GROUP BY Record_ID, Measure_ID
  ) --=== Calculate the final result.
   SELECT  Record_ID
          ,TAC = SUM((T-A)*C)
     FROM ctePivot
    GROUP BY Record_ID
  ;
  Results:
  
 Record_ID   TAC
----------- -----------
1           330
3           240


  Thank you Jeff for your prompt response.  Will try and get back to you.