tsql - How to write SQL query to select percentage with inner join? -

i have 2 table like

first table

ftid         itemname            days --------------------------------------- 1            drive                10 2            run                  5 3            read                 21 

second table

stid    ftid        completed (bit)         datetime   -----------------------------------------------------  1        1             0                    07/11/2011 2        1             1                    08/11/2011  3        1             1                    09/11/2011  4        1             0                    10/11/2011  5 

i need select both tables , data can see below

itemname days dayscompleted daysincompleted  percentagecompletion     daysleft ---------------------------------------------------------------------------------------           drive     10    2              2                40%                   6 

select        t1.itemname,       t1.days,       sum(cast(t2.completed integer))  dayscompleted ,       count(t2.stid) -  sum(cast(t2.completed integer)) daysincompleted  ,       (count(t2.stid) * 100 / t1.days))  percentagecompletion     ,       t1.days - sum(case when datetime > getdate() 1 else 0)) daysleft       table1 t1       inner join table2 t2      on t1.ftid = t2.ftid group       t1.itemname,       t1.days 

as jnk noted if completed indeed bit you'll need cast integer (or use sum/case answer). outputted percentage complete 40 if want .40 you'll need cast decimal if t1.days not decimal.