CODEWITHSUNDEEP
sqlsqlite
adayzdone
adayzdone

Reputation: 11238

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.

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

Upvotes: 1

Views: 1902

Answers (3)

Rimas
Rimas

Reputation: 6024

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

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271151

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;

Upvotes: 5

dic19
dic19

Reputation: 17971

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

See also this Q&A.

Upvotes: 1

Related Questions