title: Query Help: A view that totals some groups
body: <p>I'm looking for the correct way to structure the query for a view in order to include some totaling, etc. on the DB side (the view will be used by reporting systems).</p>
  
  <h1>Relevant Data Structure</h1>
  
  <pre><code>+---------+      +---------+
  |WORKORDER|      |WPLABOR  |
  |---------|      |---------|
  |WONUM    |+----&gt;|WONUM    |
  |...      |  +   |LABORHRS |
  +---------+  |   |RATE     |
               |   +---------+
               |
               |   +---------+
               |   |WPITEM   |
               |   |---------|
               +--&gt;|WONUM    |
               |   |ITEMQTY  |
               |   |UNITCOST |
               |   +---------+
               |
               |   +----------------+
               |   |LONGDESCRIPTION |
               |   |----------------|
               +--&gt;|LDKEY           |
                   |LDTEXT          |
                   +----------------+
  </code></pre>
  
  <h1>Goal</h1>
  
  <p>I would like to return the following:</p>
  
  <ul>
  <li>Various items from Workorder</li>
  <li>The sum of all labor costs (sum of each laborhrs*rate)</li>
  <li>The sum of all items (sum of each itemqty*unitcost)</li>
  <li>The LDText CLOB</li>
  </ul>
  
  <p>I'm to the point where I have the relevant information displayed in the query, but am struggling with the totaling of the labor and item costs. </p>
  
  <h1>Query So Far</h1>
  
  <pre><code> SELECT 
      WORKORDER.WONUM, 
      WORKORDER.ACTLABHRS, 
      WORKORDER.LOCATION, 
      WORKORDER.STATUS, 
      WORKORDER.WO7,          -- Requester
      WORKORDER.WO8,          -- Extension
      WORKORDER.WO9,          -- Location
      WORKORDER.LEADCRAFT, 
      WORKORDER.WO11,         -- Extension
      WORKORDER.GLACCOUNT, 
      WORKORDER.WO10,         -- Contact
      WORKORDER.DESCRIPTION,  -- Short description
      WORKORDER.WO6,          -- Plant rearrangement (YORN / boolean value)
      LONGDESCRIPTION.LDTEXT,
      WPLABOR.LABORHRS,
      WPLABOR.RATE,
      WPITEM.ITEMQTY,
      WPITEM.UNITCOST
    FROM   
      MAXIMO.WORKORDER
    LEFT OUTER JOIN 
      MAXIMO.LONGDESCRIPTION
        ON WORKORDER.WONUM = CAST(LONGDESCRIPTION.LDKEY as varchar(22))
  
    LEFT OUTER JOIN
      MAXIMO.WPLABOR
        ON WORKORDER.WONUM = WPLABOR.WONUM
  
    LEFT OUTER JOIN
      MAXIMO.WPITEM
        ON WORKORDER.WONUM = WPITEM.WONUM
  
    WHERE  
      LONGDESCRIPTION.LDOWNERTABLE='WORKORDER' AND
      LONGDESCRIPTION.LDOWNERCOL = 'DESCRIPTION';
  </code></pre>
  
  <p>Thanks for any help you can give!</p>