sqlite count of distinct occurrences

Question

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.

Example

Although close, these results are incorrect.

enter image description here

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

why "dog" in second row of desired result, but not "bird"? what to do with "bird"? — Rimas, Oct 17 '14 at 20:42
It does not matter if b dog, c dog, or d bird are chosen... Any ONE of them will work. — adayzdone, Oct 17 '14 at 20:44

score 4 · Answer 1 · answered Oct 17 '14 at 20:41

4

You can aggregate by colA to get most of what you want:

select colA, count(*)
from myTable
where colA in ('121', '122','123','124','125','126','127','128','129')
group by colA;

It is unclear how you are getting colB and colC. The following works for your example data:

select colA, min(colB), max(colC), count(*)
from myTable
where colA in ('121', '122','123','124','125','126','127','128','129')
group by colA;

answered Oct 17 '14 at 20:41

Gordon Linoff

1,242,037
58
646
786

Hi Gordon. Here are the results. 124 should have a 1 count and 125 and 127 should have a 2 count. http://i.imgur.com/dW39SL2.png – adayzdone Oct 17 '14 at 21:33

Rimas · Accepted Answer · 2014-10-17T22:09:50.823

2

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

edited Oct 17 '14 at 22:09

answered Oct 17 '14 at 21:00

Rimas

5,904
2
26
38

This is valid SQLite syntax; see https://www.sqlite.org/lang_with.html; tested with [SQLite Manager](https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager/) – Rimas Oct 17 '14 at 21:41
Looks like you use old SQLite version, as `WITH` clause is introduced in 3.8.3 version: http://www.sqlite.org/changes.html#version_3_8_3 – Rimas Oct 17 '14 at 21:54
Is there a way to produce the correct results without it? See my edit – adayzdone Oct 17 '14 at 22:02
Got it... Thank you. Is there a way of assigning ('121', '122','123','124','125','126','127','128','129') to a var so it doesn't have to be entered twice. It is a very long list. – adayzdone Oct 17 '14 at 22:15

score 1 · Answer 3 · edited May 23 '17 at 10:29

1

I think you are looking for a combination of both COUNT(colC) + GROUP BY (colA). Something like this:

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

sqlite count of distinct occurrences

3 Answers3