title: SQL Server 2005 - SUM'ing one field, but only for the first occurence of a second field
body: <h3>Platform: SQL Server 2005 Express</h3>
  <p>Disclaimer: I’m quite a novice to SQL and so if you are happy to help with what may be a very simple question, then I won’t be offended if you talk slowly and use small words :-)</p>
  <p>I have a table where I want to SUM the contents of multiple rows.  However, I want to SUM one column only for the first occurrence of text in a different column.</p>
  <pre><code>Table schema for table 'tblMain'
  fldOne {varchar(100)}    Example contents: “Dandelion“
  fldTwo {varchar(8)}      Example contents: “01:00:00” (represents hh:mm:ss)
  fldThree {numeric(10,0)} Example contents: “65”
  </code></pre>
  <p>Contents of table:<br></p>
  <pre>
  Row number      fldOne       fldTwo     fldThree
  ------------------------------------------------
  1               Dandelion    01:00:00   99
  2               Daisy        02:15:00   88
  3               Dandelion    00:45:00   77
  4               Dandelion    00:30:00   10
  5               Dandelion    00:15:00   200
  6               Rose         01:30:00   55
  7               Daisy        01:00:00   22
  etc. ad nausium
  </pre>
  <p>If I use:</p>
  <pre><code>Select * from tblMain where fldTwo &lt; ’05:00:00’ order by fldOne, fldTwo desc
  </code></pre>
  <p>Then all rows are correctly returned, ordered by fldOne and then fldTwo in descending order (although in the example data I've shown, all the data is already in the correct order!)</p>
  <p>What I’d like to do is get the SUM of each <em>fldThree</em>, but only from the first occurrence of each <em>fldOne</em>.</p>
  <p>So, SUM the first Dandelion, Daisy and Rose that I come across. E.g.
  99+88+55</p>
  <p>At the moment, I’m doing this programmatically;  return a RecordSet from the Select statement above, and <em>MoveNext</em> through each returned row, only adding <em>fldThree</em> to my ‘total’ if I’ve never seen the text from <em>fldOne</em> before.  It works, but most of the Select queries return over 100k rows and so it’s quite slow (slow being a relative term – it takes about 50 seconds on my setup).</p>
  <p>The actual select statement (selecting about 100k rows from 1.5m total rows) completes in under a second which is fine.  The current programatic loop is quite small and tight, it's just the number of loops through the RecordSet that takes time.  I'm using adOpenForwardOnly and adLockReadOnly when I open the record set.</p>
  <p>This is a routine that basically runs continuously as more data is added,  and also the <em>fldTwo</em> 'times' vary, so I can't be more specific with the Select statement.</p>
  <p>Everything that I’ve so far managed to do natively with SQL seems to run quickly and I’m hoping I can take the logic (and work) away from my program and get SQL to take the strain.</p>
  <p>Thanks in advance</p>