How to return all columns but GROUP BY a subset of those columns using a COUNT aggregate with Clickhouse

Question

I'm trying to return all columns from a table in ClickHouse, however I would like to count the number of students in Program as a field. Ideally the query would return University, Student, Program, student_count. The count would be how many 'Programs' there are grouped by University. An example query is below

SELECT 
    University,
    Program, 
    Student,
    COUNT(Program) as student_count
FROM 
    table
GROUP BY 
    University

Example Data:

| University  | Student   | Program   |
| ----------- | --------- | --------- |
| University A| Student 1 | Program A |
| University A| Student 2 | Program A |
| University A| Student 3 | Program B |
| University B| Student 4 | Program A |
| University B| Student 5 | Program B |

Desired Data:

| University  | Student  |Program   |student_count|
| ----------- | ---------| -------- | ----------- | 
| University A| Student 1| Program A|2            |
| University A| Student 2| Program A|2            |
| University A| Student 3| Program B|1            |
| University B| Student 4| Program A|1            |
| University B| Student 5| Program B|1            |

My question is is this even possible with ClickHouse, and if so how would it be implemented?

Answer 1

Clickhouse does support standard window functions, therefore:

SELECT 
    University,
    Program, 
    Student,
    count(*) over (partition by University, Program) as student_count
FROM 
    table;