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?
>Solution :
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';