sqlite count of distinct occurrences

What is the best way of writing a sqlite query that will count the occurrences of colC after selecting distinct colA’s ?

SELECT colA, colB, colC FROM myTable WHERE colA IN ('121', '122','123','124','125','126','127','128','129');

Notice ColA needs to be distinct.

Although close, these results are incorrect.

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

Answer

WITH t AS (
  SELECT colA, min(colB) AS colB, max(colC) AS colC
    FROM myTable
    WHERE colA IN ('121', '122','123','124','125','126','127','128','129')
    GROUP BY colA
)
SELECT t.*, c.colC_count
  FROM t
  JOIN (
    SELECT colC, count(*) AS colC_count
      FROM t
      GROUP BY colC
  ) c ON c.colC = t.colC

Explanation:

First subquery (inside WITH) gets desired result but without count column. Second subquery (inside JOIN) counts each colC value repetition in desired result and this count is returned to final result.

There very helpful WITH clause as result of first subquery is used in two places. More info: https://www.sqlite.org/lang_with.html

---

Query for SQLite before version 3.8.3:

SELECT t.*, c.colC_count
  FROM (
    SELECT colA, min(colB) AS colB, max(colC) AS colC
      FROM myTable
      WHERE colA IN ('121', '122','123','124','125','126','127','128','129')
      GROUP BY colA
    ) t
  JOIN (
    SELECT colC, count(*) AS colC_count
      FROM (
        SELECT max(colC) AS colC
          FROM myTable
          WHERE colA IN ('121', '122','123','124','125','126','127','128','129')
          GROUP BY colA
        ) c
      GROUP BY colC
  ) c ON c.colC = t.colC