title: sql query for a report
body: <p>I have a view which contains (among other columns) a header "name", and an item "rating" column. The view joins the header to the item table, so the name column from the header is repeated for every item.</p>
  
  <p>I need to run a report on the table; ideally, I would have 5 columns in the sql result; the header "name", and 4 copies of the "rating" column, where every copy of the rating column shows the count of ratings which are over a certain threshold. So rating column #1 will show the number of items in the table (for the item's header) which have a rating of 1 or higher, the second rating column will show the number of items in the table (for the same item's header) which have a rating of 5 or higher, and so on.</p>
  
  <p>I know how to get the name and 1 rating column back:</p>
  
  <pre><code>select name, count(rating) as cnt1 from myview where rating &gt; 1 group by name
  </code></pre>
  
  <p>but combining that with the other desired rating columns is failing me at the moment. Tried using a "union" clause, but that just puts multiple rows in the result list. An absolute requirement (the query has to plug into a report template, so this is very inflexible) is that I only have one row returned for every unique "name" field in the view, because otherwise the report object which is going to read the data won't know how to interpret the results.</p>
  
  <p>Any tips?</p>
  
  <p>Update
  Here is some sample data:</p>
  
  <pre><code>name, rating
  myname1, 1
  myname1, 1
  myname1, 10
  myname1, 4
  myname1, 7
  myname1, 3
  myname1, 5
  myname1, 5
  myname1, 4
  myname2, 2
  myname2, 10
  myname2, 6
  myname2, 8
  myname2, 5
  myname2, 4
  myname2, 6
  </code></pre>
  
  <p>And the desired output:</p>
  
  <pre><code>name, cnt1, cnt5, cnt7, cnt8
  myname1, 7, 2, 1, 1
  myname2, 7, 4, 2, 1
  </code></pre>
  
  <p>...where cnt1 = rating > 1, cnt5 = rating > 5, cnt7 = rating > 7, cnt8 = rating > 8</p>