CODEWITHSUNDEEP
sqlitegroup-bycountconditional-statementscase
user3768495
user3768495

Reputation: 4657

SQL Query: How to count occurrence of a specific value in a column without grouping on that column?

I have a table like this:

id | value
1  | a
1  | a
1  | b
1  | c
2  | a
2  | a
2  | a 
2  | c

And I want to count(*) by id and then count(value==a) by id, which means this is the desired results:

id | total_counts | a_counts
1  |      4       |   2 
2  |      4       |   3

I know how to do it by joining two subqueries, but is there an easier/faster way to do it? Like this pseudo-code:

SELECT id, COUNT(*) AS total_counts, COUNT(value==a) AS a_counts
FROM table
GROUP BY id

Not sure if there is a way to do the COUNT(value==a) part. Please kindly help.

Upvotes: 1

Views: 118

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175716

You could use SUM:

SELECT id, COUNT(*) AS total_counts, SUM(value='a') AS a_counts
FROM table
GROUP BY id;

Or if you have SQLite 3.25 you could use windowed version:

SELECT /*DISTINCT*/
     id,
     COUNT(*) OVER(PARTITION BY id) AS total_counts,
     COUNT(*) FILTER (WHERE value = 'a') OVER(PARTITION BY id) AS a_counts
FROM tab

Upvotes: 1

Related Questions