We help IT Professionals succeed at work.

need calculation for avg number of  counts per day.

Fordraiders
Fordraiders asked
need calculation for avg number of  counts per day.

I have 3 fields

Create_date
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



Thanks
fordraiders
Comment
Watch Question

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

Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014


SELECT AVG(completed_count) AS avg_daily_completed, COUNT(*) AS days_with_completion
FROM (
    SELECT
        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


SCOTT,  giving me a parameter prompt on DAY
this is access vba   ...Not sql server sorry
Senior DBA
Most Valuable Expert 2018
Top Expert 2014

OOPS, didn't see this was Access and not SQL Server.


Googled this help:
common approach in Access is to "stack" query. You make one query, save it and name it something then you create 2nd query and have it base on the first query.


I've touched the query up as best I can for Access.  

So maybe you can create a query with the inner/subquery below, and then base a 2nd query on that?

SELECT AVG(completed_count) AS avg_daily_completed, COUNT(*) AS days_with_completion
FROM (
    SELECT
        CDATE(create_date) AS create_day,
        COUNT(*) AS completed_count
    FROM dbo.table_name
    WHERE status = 'completed' AND
        create_date >= #01/01/2019# AND create_date < #01/01/2020#
    GROUP BY
CDATE(create_date)
) AS query1



different field names but that worked.

query1

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;

and
query2

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