title: Translate SQLite query, with subquery, into Peewee statement
body: <p>I've got a SQL statement that does what I need, but I'm having trouble converting it into the correlated Peewee statement. Here's the SQL I have now, note that I'm using a subquery right now, but I don't care that it's a subquery either way.</p>
  
  <pre><code>select t.name,
          count(a.type_id) as total,
          (
              select count(id)
              from assignment a
              where a.course_id = 7
              and a.due_date &lt; date()
                and a.type_id = t.id
              group by a.type_id
              order by a.type_id
          ) as completed
  from assignment a
  inner join type t on t.id = a.type_id
  where a.course_id = 7
  group by a.type_id
  order by a.type_id
  </code></pre>
  
  <p>Here's the closest I've come to the Peewee statement. Currently I'm aliasing a static number in the query just so that I have a value to work with in my template, so please ignore that part.</p>
  
  <pre><code>Assignment.select(
      Type.name,
      fn.Lower('1').alias('completed'),
      fn.Count(Type.id).alias('total'),
  ).naive().join(Type).where(
      Assignment.course==self,
  ).group_by(Type.id).order_by(Type.id)
  </code></pre>