Select combination of two columns, and count occurrences of this combination
django-views, mysql, sql
Solution
A straightforward `COUNT(*)` with a `GROUP BY` will do it:
select asked_by, answered_by, count(*)
from question_tbl
where session = '2011-2012'
group by 1, 2;
If you wanted a query to return data for all sessions, use this:
select session, asked_by, answered_by, count(*)
from question_tbl
group by 1, 2, 3;
Problem
I have a table with questions, where each row is a question and all questions has a asked_by and answered_by field. I want to select all unique combinations of "askers" and "answerers" and count how many times this combination of asking/answering have occurred. ``` question_tbl: id | asked_by | answered_by | session --------+----------+-------------+--------- 1 | AA | JD | 2011-2012 2 | JD | AA | 2011-2012 9 | AA | JD | 2011-2012 12 | AA | JD | 2009-2010 ``` I want to return the unique combinations in in session, and the count for this combination. E.g. for 2011-2012: ``` AA, JD, 2 JD, AA, 1 ``` PS: Ideally I'd write this for the django orm, but as I can't even seem to figure out how to formulate a sensible google search for this, to find the SQL-way is my goal. Note after problem was solved: As this turned out to be quite easy in SQL, it turned out to be quite easy in Djangos ORM too. I'll leave it here for later record: ``` from django.db.models import Count from myproject.models import Questions Questions.objects.filter(sesjonid='2011-2012').values('sporsmal_til', 'sporsmal_fra').annotate(count=Count('besvart_av')) ``` By testing how that looks in SQL it seems to me to correct (in the ./manage.py shell): ``` print str(Questions.objects.filter(sesjonid='2011-2012').values('sporsmal_til', 'sporsmal_fra').annotate(count=Count('sporsmal_til')).query) ``` This prints SQL that does exactly the same as Bohemians'.