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

Databases
1

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
2 
SELECT COUNT(*) AS tot
     , COUNT(DISTINCT CONVERT(VARCHAR(10),DateHour,112)) AS days
     , COUNT(*) /
       COUNT(DISTINCT CONVERT(VARCHAR(10),DateHour,112)) AS daily_avg
     , PageView 
  FROM [dbo].[LOG] 
GROUP 
    BY PageView 
ORDER 
    BY COUNT(*) DESC
1 Like
3

thanks!

4

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.