title: combine multiple rows into one in sql
body: <p>I have a table through which I am getting some data now given below</p>
  
  <pre><code>col1       col2 col3    col4    col5    col6    col7
  ------------------------------------------------------------
  SA106141    ABC test    1400    0       0          58800
  SA106141    ABC test    1100    53963   54    54017
  SA106141    ABC test    3000    141184  141  141325
  SA106141    ABC test    2800    154548  155  154703
  SA106141    ABC test    1500    79042   79   79121
  SA106141    ABC test    -9800   -487967 0    0
  </code></pre>
  
  <p>but the desired output should be </p>
  
  <pre><code>col1    col2    col3    col4    col5    col6    col7
  SA106141    ABC test    0      -59229   429       0
  </code></pre>
  
  <p>This is the query I have so far:</p>
  
  <pre><code>SELECT DISTINCT 
      col1, col2, col3,
      ISNULL(SUM(QTY), 0) AS col4,
      ISNULL(SUM(NET_AMT), 0) AS col5,
      ISNULL(SUM(STT), 0) AS col6,
      CASE 
         WHEN ISNULL(SUM(grand_total), 0) &lt;= 0 
            THEN 0 
            ELSE ISNULL(SUM(grand_total), 0)  
      END AS col7
  FROM
      (SELECT 
           Dates, TR_DT, TR_NO, SA, GRP, ID, SAID, Exch, Code,
           Client, SCRIPT, BUYSELL, QTY, NT_RT, NET_AMT, 
           QTY * NT_RT AS New_Net_amt,STT,
           ISNULL(NET_AMT + STT,QTY * NT_RT) AS grand_total,
           CAST(SA + GRP + ID + SAID AS VARCHAR(50)) AS testid 
       FROM  
           testtable 
       GROUP BY 
           CAST (SA + GRP + ID + SAID AS VARCHAR(50)), Dates, TR_DT,
           TR_NO, Exch, Code, Client, SCRIPT, BUYSELL, QTY,
           NT_RT, NET_AMT, STT, SA, GRP, ID, SAID) t 
  GROUP BY 
      t.testid, t.Dates, t.Client, t.SCRIPT 
  ORDER BY 
      t.Client, t.testid, t.SCRIPT, t.Dates ASC
  </code></pre>
  
  <p>Please help</p>