I am super new to SQL and am trying to figure out how to find the average by day. So YTD what were they averaging by day.

the table below is an example of the table I am working with

Study Date | ID  | Subject    
01\01\2018 | 123 | Math    
01\01\2018 | 456 | Science    
01\02\2018 | 789 | Science    
01\02\2018 | 012 | History    
01\03\2018 | 345 | Science    
01\03\2018 | 678 | History    
01\03\2018 | 921 | Art    
01\03\2018 | 223 | Science    
01\04\2018 | 256 | English

For instance, If I filter on just 'Science' as the Subject, the output I am looking for is , out of the 4 science subject results, what is the daily average, min and max YTD.

So my max in a day would be 2 science subjects, my min would be 1 etc.

how can i configure a query to output this information?

So far I know to get the YTD total it would be

select SUBJECT, count (ID)
from table
where SUBJECT='science' and year (Study_date)=2022
group by SUBJECT 

what would be the next step I have to take?

CodePudding user response：

If you want the min/max of the daily subject count, then you need two levels of aggregation:

select subject, sum(cnt_daily) as cnt, 
    min(cnt_daily) as min_cnt_daily, max(cnt_daily) as max_cnt_daily
from (
    select study_date, subject, count(*) as cnt_daily
    from mytable
    where study_date >= '2022-01-01'
    group by study_date, subject
) t
group by subject

The subquery aggregates by day and by subject, and computes the number of occurences in each group. Then, the outer query groups by subject only, and computes the total count (that's the sum of the intermediate counts), along with the min/max daily values.

CodePudding user response：

select   Subject
        ,count(*)     as total_count
        ,min(cnt)     as min_daily_count
        ,max(cnt)     as max_daily_count
        ,avg(cnt*1.0) as avg_daily_count
from
(
select   *
        ,count(*) over(partition by Study_Date, Subject) as cnt
from     t 
) t
group by Subject

Fiddle