Sub Query help required

Question

I need to get 2 summed figures however im having issues as one will be for total orders and one is for orders incomplete. These use the same initial query however the orders incomplete has an additional where clause. Can these be put into a query so that i just get the 2 columns. I have done inner queries before but i have never done one with 2 different where clauses?! Any ideas much apprciated

Query im using for total orders is:

SELECT  Count(TBL_PROPERTY.PROPREF) AS TotalOrders

FROM    TBL_PROPERTY INNER JOIN 
     TBL_REPAIR_ORDER ON TBL_PROPERTY.PROPREF = TBL_REPAIR_ORDER.PROPREF INNER JOIN 
     TBL_REPAIR_VISIT ON TBL_REPAIR_ORDER.ORDERID = TBL_REPAIR_VISIT.ORDERID INNER JOIN
     tbl_contract ON tbl_repair_order.CONTRACT = tbl_contract.CONTRACT

WHERE   (TBL_CONTRACT.CONTRACT IN ('STE')) AND
     (TBL_REPAIR_ORDER.RAISEDDATE  BETWEEN '01/12/2008' AND DATEADD(hh,23,'01/01/2009'))

Query im using for Orders Incomplete:

SELECT  Count(TBL_PROPERTY.PROPREF) AS TotalOrders

FROM    TBL_PROPERTY INNER JOIN 
     TBL_REPAIR_ORDER ON TBL_PROPERTY.PROPREF = TBL_REPAIR_ORDER.PROPREF INNER JOIN 
     TBL_REPAIR_VISIT ON TBL_REPAIR_ORDER.ORDERID = TBL_REPAIR_VISIT.ORDERID INNER JOIN
     tbl_contract ON tbl_repair_order.CONTRACT = tbl_contract.CONTRACT

WHERE   (TBL_CONTRACT.CONTRACT IN ('STE')) AND
     (TBL_REPAIR_ORDER.RAISEDDATE  BETWEEN '01/12/2008' AND DATEADD(hh,23,'01/01/2009')) AND
 TBL_REPAIR_ORDER.STATUS <> 'Completed')

Answer 1

SELECT  Count(TBL_PROPERTY.PROPREF) AS TotalOrders
      , SUM( CASE WHEN TBL_REPAIR_ORDER.STATUS <> 'Completed' THEN 1 ELSE 0 END ) AS TotalNotCompleted

Remove the TBL_REPAIR_ORDER.STATUS <> 'Completed' from the WHERE clause, too.

Answer 2

Use the two queries you have as tables/columns in a main query.