I'm developing a small little side project for work, and the final query I need to write is naturally the most complicated. I'm an SQL newbie, so any help would be greatly appreciated. The relevant schema information is as follows:
CREATE TABLE INTERACTIONS(ID TEXT NOT NULL, DATE TEXT, TYPE INT, INFO TEXT)
CREATE TABLE TYPE(ID INT PRIMARY KEY NOT NULL, LABEL TEXT)
CREATE TABLE STUDENTS(ID TEXT NOT NULL, ..., SCHOOL TEXT)
The ... is some other information but it shouldn't be relevant to the query being performed. The table I'm trying to output would look like this:
School1|School2|School3|School4|...|SchoolN
Type1 0 5 12 3 0 0
Type2 14 7 0 6 9 15
Type3 9 4 3 18 7 3
TypeN 5 5 5 5 5 5
In summary, I would like to create a table which shows how many of each type of interaction happened at each school.
A couple of things I've considered, and I guess these are possibilities, is to write separate queries for either each school or each type. As an example:
SELECT COUNT(*) FROM INTERACTIONS JOIN STUDENTS
ON INTERACTIONS.ID = STUDENTS.ID
WHERE TYPE = 1
ORDER BY STUDENTS.SCHOOL
Or
SELECT COUNT(*) FROM INTERACTIONS JOIN STUDENTS
ON INTERACTIONS.ID = STUDENTS.ID
WHERE SCHOOL LIKE "School Name"
ORDER BY INTERACTIONS.TYPE
I figured there is probably a query I can use that would output the table I'm looking for which would not require writing separate queries for every single school/type of interaction. Any help would be greatly appreciated.
