SQLite database table table1
user command date location
---------- ---------- ---------- ----------
user1 cmd1 2015-01-01 de
user2 cmd1 2015-01-01 fr
user3 cmd1 2015-01-01 uk
user1 cmd1 2015-01-01 de
...
Expected output
Output for where command='cmd1':
month users-de users-fr users-es
-------- -------- -------- --------
01 1 0 5
02 2 0 0
03 0 2 1
04 5 0 15
05 1 0 4
06 11 1 2
07 9 0 3
08 1 0 5
09 0 0 5
10 0 0 0
11 1 0 0
12 1 4 5
It is grouped by month (from column date) and also grouped by location (from column location).
Actual output
I can achieve this (per location):
month users-de
-------- --------
01 1
02 2
03 0
...
12 1
using this query:
select strftime('%m',date) as month, count(distinct user) as users-de
from table1
where command='cmd1' and location='de'
group by strftime('%m',date);
I then repeat this query for the other locations (where ... and location='fr'):
month users-fr
-------- --------
01 0
02 0
03 2
...
12 4
and (where ... and location='es');
month users-es
-------- --------
01 5
02 0
03 1
...
12 5
Is there a way to have all the users-xx columns in one table (as output from SQLite and not through any external (downstream) processing)?
Am I thinking about this in the wrong way (grouping instead of subqueries in the top select)?
Aucun commentaire:
Enregistrer un commentaire