Trying to get the average of a count resultset using sql server 2016

Question

Hi,

I have the following SQL using SQL server 2016 (RTM-GDR) (KB4058560) - 13.0.1745.2 (X64) version:

SELECT 
    COUNT(*) AS tot,
    PageView 
FROM
    [dbo].[LOG] 
GROUP BY
    PageView 
ORDER BY
    COUNT(*) DESC;

now I am getting this resultset

tot     PageView    
7893    APP 1
1476    APP 2
478     APP 3

I want to get the average daily accesses of this list

I did not get it to work

SELECT AVG(COUNT) AS avg,
       t 
FROM
    (
    SELECT 
        COUNT(*) AS COUNT,
        T.PageView t 
    FROM
        [dbo].[LOG] T 
    WHERE
        T.DateHour = (SELECT MAX (B.DateHour) FROM [dbo].[LOG] B WHERE (B.Id = T.Id)) 
    GROUP BY
        T.PageView 
    ) AS counts 
GROUP BY
    t;

avg     PageView
7893    APP 1
1476    APP 2
478     APP 3

My table structure below

-- ----------------------------
-- Table structure for LOG
-- ----------------------------   
CREATE TABLE [dbo].[LOG] (
  [id] bigint  IDENTITY(1,1) NOT NULL,
  [user] nvarchar(10) COLLATE Latin1_General_CI_AS  NULL,
  [PageView] nvarchar(100) COLLATE Latin1_General_CI_AS  NULL,
  [DateHour] smalldatetime DEFAULT getdate() NULL
)
GO

update sample data

I want to get the average daily accesses of this list

For example:

1. for PageView equal to App 1 I have 24 rows stored divided into two days (18-19/12/2017). The average is 24/2 = 12. id;user;PageView;DateHour
   10;97697;APP 1;18/12/2017 18:12:00
   11;97697;APP 1;18/12/2017 18:15:00
   12;97697;APP 2;18/12/2017 18:15:00
   13;97697;APP 2;18/12/2017 18:18:00
   14;97697;APP 2;18/12/2017 18:25:00
   15;15841;APP 1;18/12/2017 18:31:00
   16;15841;APP 1;18/12/2017 18:53:00
   17;15841;APP 1;18/12/2017 19:04:00
   18;15841;APP 1;18/12/2017 19:09:00
   19;15841;APP 2;18/12/2017 19:10:00
   20;09623;APP 1;18/12/2017 20:15:00
   21;23265;APP 1;18/12/2017 21:05:00
   22;09623;APP 1;18/12/2017 21:29:00
   23;25506;APP 1;18/12/2017 21:49:00
   24;20986;APP 1;18/12/2017 22:33:00
   25;25623;APP 1;18/12/2017 23:46:00
   26;95932;APP 1;19/12/2017 00:02:00
   27;29067;APP 1;19/12/2017 00:03:00
   28;32337;APP 1;19/12/2017 00:09:00
   29;23265;APP 1;19/12/2017 00:16:00
   30;09343;APP 1;19/12/2017 00:20:00
   31;20901;APP 1;19/12/2017 00:35:00
   32;26786;APP 1;19/12/2017 00:47:00
   33;24980;APP 1;19/12/2017 00:49:00
   34;25996;APP 1;19/12/2017 00:52:00
   35;14949;APP 1;19/12/2017 01:03:00
   36;54987;APP 1;19/12/2017 01:47:00
   37;23265;APP 1;19/12/2017 04:53:00
   38;20635;APP 3;19/12/2017 06:14:00
   39;25081;APP 3;19/12/2017 06:38:00
   40;61660;APP 3;19/12/2017 06:42:00

Answer 1

Hi @Santiago Vidaurri Bernal ,

Please also check：

SELECT PageView,SUM(cc)/COUNT(cc) avg  
FROM (SELECT PageView,COUNT(PageView) cc  
      FROM [LOG]  
      GROUP BY PageView,DATEPART(DD,DATEHOUR)) t  
GROUP BY PageView  
ORDER BY PageView  

Output:

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Check this query:

select 
    cast(COUNT(*) as float) / count(distinct cast([DateHour] as date)) as avg,
    PageView
from LOG
group by PageView

Do not cast to float if it is not needed.