title: SQL Query to use in report builder 3.0: grouping results together
body: <p>I am trying to product a sales report that shows for example</p>
  
  <pre><code>itemid          salesdate       qty_sold
  item1           1/9/14          3
  item1           1/9/14          2
  item1           1/9/14          5
  item2           2/9/14          2
  item3           4/9/14          1
  </code></pre>
  
  <p>The problem I have is because the sales of for example 3 on Monday could be made up of 1-3 different sales orders, I can only get it to show multiple lines and not group together as I want.</p>
  
  <p>I want to product a report which shows item id, date of purchase and total sold on that day, not a list of all sales to include that item if that makes sense?</p>
  
  <p>thanks in advance</p>
  
  <p>more details:</p>
  
  <pre><code>    SELECT st.orderdate, sl.itemid, sl.qty
        FROM salestable st
  INNER JOIN salesline sl
          ON salestable.salesid = salesline.salesid
  </code></pre>
  
  <p>currently, it displays results as follows. </p>
  
  <pre><code>OrderDate     ItemId     Qty    
  1/1/14        101        1
  1/1/14        101        3
  1/1/14        102        1
  </code></pre>
  
  <p>I would like to group the rows if possible to only show 1 line per date &amp; itemid. it doesn't work because they are obviously separate lines in the database as they have different order numbers etc. </p>
  
  <pre><code>OrderDate     ItemId    Qty
  1/1/14        101       4
  1/1/14        102       1
  2/1/14        102       5
  2/1/14        101       2
  </code></pre>
  
  <p>If it cant be done, then a grouping type within report builder would suffice but I cant see a way of doing it!</p>
  
  <p>Cheers</p>