title: SQLAlchemy Subquery for Suming values from another table
body: <p>I am struggling to understand the proper syntax for executing a subquery in SQLAlchemy when the value needs to be returned (i.e. not used in the WHERE).</p>
  
  <p>I am using the declarative approach.</p>
  
  <p>There are two models being used:</p>
  
  <pre><code>class ProjectInvoices(Base):
      InvoiceID = Column(Integer(unsigned=True), default=0, primary_key=True, autoincrement=True)
      MasterProjectID = Column(Integer(unsigned=True), index=True, nullable=False)
      ExpenseAmount = Column(Numeric(10, 2), default=0)
      HoursAmount = Column(Numeric(10, 2), default=0)
      IsVoid = Column(Boolean, default=0, index=True)
      IsSubmit = Column(Boolean, default=0, index=True)
  
  class ProjectMasters(Base):
      MasterProjectID = Column(Integer(unsigned=True), default=0, primary_key=True, autoincrement=True)
      MasterProjectName = Column(Unicode(255))
      MasterProjectMemo = Column(UnicodeText)
      IsActive = Column(Boolean, default=0, index=True)
  </code></pre>
  
  <p>The point of the query is to determine the current amount invoiced for each project by using a subquery to sum the related invoices.  There are other reasons this is being done in a subquery rather than just a join so I really need to figure out the subquery issue.</p>
  
  <p>This is an example of my current SA query:</p>
  
  <pre><code>sqry = session.query(
    func.sum(
      func.ifnull(ProjectInvoices.ExpenseAmount, 0) 
      + func.ifnull(ProjectInvoices.HoursAmount, 0))).label('billed_total')
  ).filter(and_(ProjectInvoices.IsVoid == 0, ProjectInvoices.IsSubmit == 1)
  ).subquery()
  
  result = session.query(
    ProjectMasters.MasterProjectID, 
    ProjectMasters.MasterProjectName, 
    sqry.columns.billed_total.label('invoice_total')
  ).filter(ProjectMasters.IsActive == 1).all()
  </code></pre>
  
  <p>I have a feeling this is going to be embarrassing simple but I can't seem to crack the code on getting this to work.</p>
  
  <p>I have tried just about every sample I can find with mixed results.  If I leave out the .correlate() argument I receive the following error:</p>
  
  <pre><code>'Alias' object has no attribute 'MasterProjectID'
  </code></pre>
  
  <p>I have also tried adding the following statement to the end of the subquery() without any luck:</p>
  
  <pre><code>.correlate(ProjectMasters.MasterProjectID, ProjectInvoices.MasterProjectID)
  </code></pre>
  
  <p>If I do include the correlate argument then I receive the following error:</p>
  
  <pre><code>TypeError: Boolean value of this clause is not defined
  </code></pre>
  
  <p>Thanks for the help in advance...</p>