Can repeating query be saved?

user_48778 0
Viewed 37

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?

count distinct group-by sqlite python
2 Answers

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';
user_104988280

If your q variable is a string of the query to be executed you can do what you want with loops:

for i in [1, 2, 9]:
    q=f"SELECT COUNT(1) FROM (SELECT a{i} FROM tableX WHERE freq>=1.6 AND a3='n' AND a4='y' AND a5='q' GROUP BY a{i})"
user_115301640
Related