sql - sqlite count of distinct occurences -

sql - sqlite count of distinct occurences -

what best way of writing sqlite query count occurrences of colc after selecting distinct cola's ?

select cola, colb, colc mytable cola in ('121', '122','123','124','125','126','127','128','129');

notice cola needs distinct.

although close, these results incorrect.

it should return: 123 cat 1 124 b dog 1 125 e snake 2 126 f fish 1 127 g snake 2

with t ( select cola, min(colb) colb, max(colc) colc mytable cola in ('121', '122','123','124','125','126','127','128','129') grouping cola ) select t.*, c.colc_count t bring together ( select colc, count(*) colc_count t grouping colc ) c on c.colc = t.colc

explanation:

first subquery (inside with) gets desired result without count column. sec subquery (inside join) counts each colc value repetition in desired result , count returned final result.

there helpful with clause result of first subquery used in 2 places. more info: https://www.sqlite.org/lang_with.html

query sqlite before version 3.8.3:

select t.*, c.colc_count ( select cola, min(colb) colb, max(colc) colc mytable cola in ('121', '122','123','124','125','126','127','128','129') grouping cola ) t bring together ( select colc, count(*) colc_count ( select max(colc) colc mytable cola in ('121', '122','123','124','125','126','127','128','129') grouping cola ) c grouping colc ) c on c.colc = t.colc

sql sqlite