How to Get Multiple Counts With Single Query in SQLite?

In data analysis, obtaining multiple counts for different categories is a common requirement. SQLite, a lightweight and versatile database management system, offers a powerful feature that allows us to achieve this efficiently.

In this article, we'll explore how to use SQLite to retrieve multiple counts with a single query, simplifying data analysis tasks.

How to Get Multiple Counts With a Single Query?

When analyzing data, it is often necessary to count occurrences of different categories within a dataset. For example, we might want to count the number of sales by region, the number of products in each category or the number of users in each age group. Manually querying each count separately can be tedious and inefficient. Let's understand using the below method are as follow:

1. Using CASE Statements and GROUP BY Clause.
   
2. Using Subquery

Let's set up the environment to get multiple counts with a single query

Let us first start by creating a table and inserting some records in it. We will later use this table in our examples.

CREATE TABLE test 
(
  id INT PRIMARY KEY,
  val1 VARCHAR(50),
  val2 VARCHAR(50)
);

INSERT INTO test VALUES (1, 'type1', 'red');
INSERT INTO test VALUES (2, 'type2', 'red');
INSERT INTO test VALUES (3, 'type3', 'blue');
INSERT INTO test VALUES (4, 'type1', 'green');
INSERT INTO test VALUES (5, 'type2', 'red');
INSERT INTO test VALUES (6, 'type3', 'blue');
INSERT INTO test VALUES (7, 'type1', 'green');
INSERT INTO test VALUES (8, 'type2', 'green');
INSERT INTO test VALUES (9, 'type3', 'red');
INSERT INTO test VALUES (10, 'type1', 'blue');
INSERT INTO test VALUES (11, 'type2', 'red');
INSERT INTO test VALUES (12, 'type3', 'green');
INSERT INTO test VALUES (13, 'type1', 'red');
INSERT INTO test VALUES (14, 'type2', 'green');
INSERT INTO test VALUES (15, 'type3', 'red');

Output:

1. Using CASE Statements and GROUP BY Clause

The problem is to analyze the count of occurrences of different values (red, blue, green) in the val2 column for each distinct value in the val1 column in the test table. The query calculates these counts using SUM() function with CASE statements for each value, groups the results by val1, and orders them by val1.

Query:

SELECT val1,
  SUM(CASE WHEN val2='red' THEN 1 ELSE 0 END) AS red_cnt,
  SUM(CASE WHEN val2='blue' THEN 1 ELSE 0 END) AS blue_cnt,
  SUM(CASE WHEN val2='green' THEN 1 ELSE 0 END) AS green_cnt
FROM test
GROUP BY val1
ORDER BY val1;

Output:

Explanation: As we can see, using the CASE statements we were able to get multiple counts.

2. Using Subquery

We can make use of subqueries to find multiple count by utilising WHERE clause in the subquery to only select the respective count for all the conditions. The following query performs the above count using the subquery method.

Query:

SELECT t.val1,
  (SELECT COUNT(*) from test WHERE val2='red' and val1=t.val1) AS red_cnt,
  (SELECT COUNT(*) from test WHERE val2='blue' and val1=t.val1) AS blue_cnt,
  (SELECT COUNT(*) from test WHERE val2='green' and val1=t.val1) AS green_cnt
FROM (SELECT DISTINCT val1 FROM test) t
ORDER BY t.val1;

Output:

Explanation: As we can see, in the subquery we only selected those records which were applicable to the respective count.

Conclusion

In this article, we first started by looking at what CASE statements are and understood how we can use CASE statements. We later, used the SUM() function and CASE statements to find multiple counts in a single query. Finally, we went through an advanced example to solidify our understanding.