Two distinct aggregate queries combined: possible? (EF Core 5, SQL Server)-CodePudding

I've got a table like this:

| id (PK) | name  | course  |  date      | grade
 --------- ------- --------- ------------ ------
|    1    |  Jane | french  | 2016-11-20 | 20
|    3    |  CARL | french  | 2015-09-02 | 30
|    4    |  Anna | french  | 2016-11-20 | 25
|    5    |  JON  | french  | 2016-09-02 | 56
|    6    | Linda | english | 2016-09-02 | 22
|    7    |  TIM  | english | 2016-11-20 | 23
|    8    |  JON  | english | 2016-11-20 | 44

and I'm wondering if it's possible to answer the following two questions with a single query:

What is the average grade when grouping by course, across all enrolled students in all dates per the course? (this seems more straightforward, as roughly, group on several columns and then take the average of the 'grade' column).
What is the average number of students that enroll in each course by date? Phrased differently, "in 'English,' since two students were enrolled on 11-20, and one student was enrolled on 09-02, the average number of enrolled students per class date is 1.5.

Is it possible to answer both of these questions with a single query? Or, is it such that because I'd be "grouping by" different columns, I can't achieve this in a single query?

I'm using EF Core 5.0, backed by a SQL Server database, so it would be slick if I could achieve this using the LINQ groupby operator, but if not, I don't mind writing straight SQL if it's more efficient.

CodePudding user response：

One of the solutions is using Scalar-functions in SQL-server These functions can be used as a column and return int,nvarchar, date and ... here is an example to use with ef core: ef core scalar function

CodePudding user response：

I'm going to answer this with SQL Server T-SQL syntax because I've just found it more beneficial to wrap my logic into procedures that I can update dynamically without having to recompile the web server. This makes updating query results a breeze and seamless to the server and end-user whereas with Entity Framework, it will require a recompile anytime there are changes.

Question 1, the easiest way to do this is with a select on a sub query where you are selecting all of the courses and grades available, and then you are running an average AVG() on all of the grades per course using a group by (and the A alias is important because SQL server typically won't allow selection from a subquery without a name value, although the name A is really irrelevant):

SELECT COURSE, AVG(GRADE) FROM (
    SELECT COURSE, GRADE FROM MyTable 
) A
GROUP BY A.COURSE

Question 2, you don't need a sub query for this one. You just need to group by the course and date and get the aggregate COUNT(*) of all records of students per course and date. The resulting list will be list of courses and dates and the number of students in that course on that date.

SELECT 
    COURSE, [DATE], COUNT(*) AS TOTALSTUDENTS
FROM MyTable
GROUP BY COURSE, [DATE]

As far as your last question goes, since these are two different data sets, I would put them in one procedure and query using a multiple result set.

I hope that helps!