CODEWITHSUNDEEP
sqlsqlite
J01
J01

Reputation: 153

count number of times each value appears in a particular column with additional condition

The following code counts the number of times each value appears in a particular column. I have no problem with this, and running the code gives me a result of ('A', 2), ('B', 2), ('C', 1).

However, i wish to add in an additional condition which I am having a problem with. If the text value appears more than once for the same group, it would only be counted as one. For example, the value "A" in the following table belongs to the same group (1). Which means that the count for "A" would be ('A', 1) instead of ('A', 2)

Group Text
1 A
1 A
1 B
2 C
3 B 
SELECT text, count(*)
FROM table
GROUP BY text

Upvotes: 1

Views: 412

Answers (1)

Patrick Parker
Patrick Parker

Reputation: 4967

use COUNT(DISTINCT "Group") instead of COUNT(*)

Note that I have demarcated the column name "Group" with double quotes since it is an SQLite keyword. See here for details: https://www.sqlite.org/lang_keywords.html

Upvotes: 2

Related Questions