sqlite - distinct values as new columns & count -

i'm trying generate summary table using sqlite below.

i need aggregate 1) number of times each model driven, 2) total distance driven & 3) distinct values driver col & count number of times each driver has driven particular model - group modelwith count(model) & sum(distance) 1 & 2 - `i need last part #3 , right approach find number of occurrences each distinct values of column , add them new columns each model ?

my table is:

id  model  datetime     driver   distance ---|-----|------------|--------|--------- 1  | s   | 04/03/2009 | john   | 399  2  | x   | 04/03/2009 | juliet | 244 3  | 3   | 04/03/2009 | borat  | 555 4  | 3   | 03/03/2009 | john   | 300 5  | x   | 03/03/2009 | juliet | 200 6  | x   | 03/03/2009 | borat  | 500 7  | s   | 24/12/2008 | borat  | 600 8  | x   | 01/01/2009 | borat  | 700 

result be

id  model| drives   distance  john   juliet  borat ---|-----|--------|---------|------|------ |------ 1  | s   | 2      | 999     | 1    |   0   |  1 2  | x   | 4      | 1644    | 0    |   2   |  2 3  | 3   | 2      | 855     | 1    |   0   |  1 

ok... time got it!

select new_table.model, count (new_table.model) drives, sum (new_table.distance) distance,         sum(case when driver = 'john' 1 else 0 end) john,        sum(case when driver = 'juliet' 1 else 0 end) juliet,        sum(case when driver = 'borat' 1 else 0 end) borat new_table group model