Link to home
Create AccountLog in
Avatar of Fordraiders
FordraidersFlag for United States of America

asked on

need calculation for avg number of counts per day.

need calculation for avg number of  counts per day.

I have 3 fields

status  - text
nsc_id  - primary id autonumber

I have about 30,00 records in table
i.e. below

12/1/2019 7:11:43 AM       completed         1
12/1/2019 7:00:43 AM       completed         2
12/1/2019 7:12:43 AM       completed         3
12/2/2019 7:05:43 AM       open                   4
12/2/2019 7:11:43 AM       completed         5
12/2/2019 7:00:43 AM       completed         6
12/2/2019 7:12:43 AM       open                   7
12/2/2019 7:05:43 AM       open                   8

What I need:

I will be looking at all 2019 records so i need to know on the avg how many status that are "completed" for a given day

on 12/1   3 were completed
on 12/2   2 were completed

so on the avg 2.5 are completed in a given day

Avatar of smilieface
Flag of Australia image

This should be close

with cte_cnt as (
select count(*) as dayCount
from <yourTable>
where status = "completed"
group by convert(date, Create_Date)
select avg(dayCount) as dayAverage
from cte_cnt

Open in new window

SELECT AVG(completed_count) AS avg_daily_completed, COUNT(*) AS days_with_completion
        DATEADD(DAY, DATEDIFF(DAY, 0, create_date), 0) AS create_day,
        COUNT(*) AS completed_count
    FROM dbo.table_name
    WHERE status = 'completed' AND
        create_date >= '20190101' AND create_date < '20200101'
    GROUP BY DATEADD(DAY, DATEDIFF(DAY, 0, create_date), 0)
) AS query1

Avatar of Fordraiders


SCOTT,  giving me a parameter prompt on DAY
this is access vba   ...Not sql server sorry
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
different field names but that worked.


SELECT Count(dbo_t_nsc_trackcode_assigned_DataEntry.NSC_Id) AS CountOfNSC_Id, DateValue([opened_date]) AS DateOpen
FROM assigned_DataEntry
GROUP BY DateValue([opened_date])
ORDER BY DateValue([opened_date]) DESC;


SELECT Avg(CountPerDay.CountOfNSC_Id) AS AvgOfCountOfNSC_Id
FROM CountPerDay
thanks scott