sqlite3 group by

Question

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     
----------  ----------  -----------  ----------  ----------
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

mechanical_meat · Accepted Answer · 2010-11-11 20:40:05Z

3

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;

answered Nov 11, 2010 at 20:40

mechanical_meat

170k25 gold badges237 silver badges231 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

name Over a year ago

thx adam. It works just fine. Could explain why I have to use in a group-by all non-aggregated columns? Is it because some of them can have null values? thx.

mechanical_meat Over a year ago

Cheers, mate. Taking another look at your query it appears that, with fun.*, that you were in fact grouping-by all non-aggregated columns. If you hadn't done so, you would likely notice that your resultset would be smaller than expected.

Doug Currie · Accepted Answer · 2010-11-11 20:48:14Z

1

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);

answered Nov 11, 2010 at 20:48

Doug Currie

41.4k1 gold badge103 silver badges121 bronze badges

Collectives™ on Stack Overflow

sqlite3 group by

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related