In my Python / Sqlite program, I am running queries like this
JavaScript
x
4
1
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)
2
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)
3
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)
4
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?
Advertisement
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:
JavaScript
1
6
1
SELECT COUNT(DISTINCT a1) AS q1,
2
COUNT(DISTINCT a2) AS q2,
3
COUNT(DISTINCT a9) AS q9
4
FROM tableX
5
WHERE freq >= 1.6 AND a3 = 'n' AND a4 = 'y' AND a5 = 'q';
6