title: Getting Grand Totals using Group By
body: <p>I am trying to teach myself more about SQL at the moment and am currently trying to carry out some simple sales reporting using <code>SUM</code>, <code>COUNT</code>, <code>AVG</code> and <code>GROUP BY</code> functions within a SQL Server 2008 database. I have managed to get the total, count and average of each group by row. </p>
  
  <p>How do I get the grand total of all the group by rows? </p>
  
  <p>SQL so far:</p>
  
  <pre><code>SELECT 
      SUM(dbo.tbl_orderitems.mon_orditems_pprice) AS prodTotal,
      AVG(dbo.tbl_orderitems.mon_orditems_pprice) AS avgPrice,
      count(dbo.tbl_orderitems.uid_orditems_prodid) AS prodQty,
      dbo.tbl_orderitems.txt_orditems_pname
  FROM 
      dbo.tbl_orderitems
  INNER JOIN 
      dbo.tbl_orders 
      ON (dbo.tbl_orderitems.uid_orditems_orderid = dbo.tbl_orders.uid_orders)
  WHERE 
      dbo.tbl_orders.uid_order_webid = 
         &lt;cfqueryparam cfsqltype="cf_sql_integer" value="#session.webid#"&gt;
      AND dbo.tbl_orders.txt_order_status = 
         &lt;cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.sale_status#"&gt;
  GROUP BY
      dbo.tbl_orderitems.txt_orditems_pname
  </code></pre>
  
  <pre>
  Product                           Qty      Gross        Avg  
  
  Westbury Climbing Frame           17       8,023.00     471.94
  Sandpoint Deluxe Climbing Frame   34       36,146.00    1,063.12
  Roseberry Climbing Frame          9        7,441.00     826.78
  Ridgeview Texas Climbing Frame    10       6,990.00     699
  Selwood Picnic Table              9        489.92       54.44
  
  I need the Totals of qty column and gross column
  </pre>
  
  <p>Many thanks</p>
  
  <p>Jason</p>