sqlite3 group by

I have 2 tables fun (master) and blk (slave).

sqlite> select * from fun;
id          mod_id      name         freq      
----------  ----------  -----------  ----------
1           1           adpcm_coder  99108     
2           1           adpcm_decod  0         

I want to count how many blk's there are for fun, so I use "group by":

sqlite> SELECT fun.*, count(blk.id) as no_blk FROM fun, blk WHERE fun.id=blk.fun_id GROUP BY (blk.fun_id);
id          mod_id      name         freq        no_blk     
---------- 开发者_JAVA百科 ----------  -----------  ----------  ----------
1           1           adpcm_coder  99108       12        

The 2nd row is rejected since blks for it do not exists. Howto get result like this?

id          mod_id      name         freq        no_blk     
----------  ----------  -----------  ----------  ----------
1           1           adpcm_coder  99108       12        
2           1           adpcm_decod  0           0

You want an OUTER JOIN.
Also you should GROUP BY all non-aggregated columns in your SELECT clause.

Try something like this:

  SELECT fun.id, fun.mod_id, fun.name, fun.freq, count(blk.id) as no_blk 
    FROM fun
         LEFT OUTER JOIN blk
         ON blk.fun_id = fun.id
GROUP BY fun.id, fun.mod_id, fun.name, fun.freq;

Adam is correct; another approach:

SELECT fun.*, count(blk.id) as no_blk FROM fun, blk 
WHERE fun.id=blk.fun_id GROUP BY (blk.fun_id) 
UNION 
SELECT fun.*, 0 as no_blk FROM fun, blk 
WHERE fun.id not in (SELECT fun.id from fun, blk WHERE fun.id=blk.fun_id);