Can repeating query be saved?

In my Python / Sqlite program, I am running queries like this

q=SELECT COUNT(1) FROM (SELECT a1 FROM tableX WHERE freq>=1.6 AND a3='n' AND a4='y' AND a5='q' GROUP BY a1)
q=SELECT COUNT(1) FROM (SELECT a2 FROM tableX WHERE freq>=1.6 AND a3='n' AND a4='y' AND a5='q' GROUP BY a2)
q=SELECT COUNT(1) FROM (SELECT a9 FROM tableX WHERE freq>=1.6 AND a3='n' AND a4='y' AND a5='q' GROUP BY a9)

So the “basic” query is the same, and the rows Sqlite gathers are the same, but because of the different grouping , I have to run the same query multiple times.

I wonder if there is a way to achieve the same output more effectively, ie. run the query only once?

Answer

In each of your subqueries, although you are using GROUP BY, you are not doing any aggregation, so I think that all you want is to count the distinct aX for each case.

You can do this in a single query and return all the counters as different columns:

SELECT COUNT(DISTINCT a1) AS q1,
       COUNT(DISTINCT a2) AS q2,
       COUNT(DISTINCT a9) AS q9
FROM tableX
WHERE freq >= 1.6 AND a3 = 'n' AND a4 = 'y' AND a5 = 'q';