Calculate Average of each group in SQL Server

comunida...

SOLVED CLOSED ACTIVE ANSWERED
User: comunidadmexicanaroma
Posted: on Jun 14, 2021 10:06 PM
Forum: SQL Server
Answer: 1
Views: 1391

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 result set

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:

for PageView equal to App 1 I have 24 rows stored divided into two days (18-19/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

Download FREE API for Word, Excel and PDF in ASP.Net

dharmend...

0 2

Reply
ANSWER
User: dharmendr
Replied: on Jun 15, 2021 05:42 AM
Modified: on Jan 24, 2022 09:46 AM
Report

Hi comunidadmexi...,

Refer below query.

SQL

DECLARE @LOG AS TABLE
(
  [id] bigint  IDENTITY(1,1) NOT NULL,
  [user] nvarchar(10) NULL,
  [PageView] nvarchar(100) NULL,
  [DateHour] smalldatetime DEFAULT getdate() NULL
)

INSERT INTO @LOG VALUES('97697','APP 1','12/18/2017 18:12:00')
INSERT INTO @LOG VALUES('97697','APP 1','12/18/2017 18:15:00')
INSERT INTO @LOG VALUES('15841','APP 1','12/18/2017 18:31:00')
INSERT INTO @LOG VALUES('15841','APP 1','12/18/2017 18:53:00')
INSERT INTO @LOG VALUES('15841','APP 1','12/18/2017 19:04:00')
INSERT INTO @LOG VALUES('15841','APP 1','12/18/2017 19:09:00')
INSERT INTO @LOG VALUES('09623','APP 1','12/18/2017 20:15:00')
INSERT INTO @LOG VALUES('23265','APP 1','12/18/2017 21:05:00')
INSERT INTO @LOG VALUES('09623','APP 1','12/18/2017 21:29:00')
INSERT INTO @LOG VALUES('25506','APP 1','12/18/2017 21:49:00')
INSERT INTO @LOG VALUES('20986','APP 1','12/18/2017 22:33:00')
INSERT INTO @LOG VALUES('25623','APP 1','12/18/2017 23:46:00')
INSERT INTO @LOG VALUES('95932','APP 1','12/19/2017 00:02:00')
INSERT INTO @LOG VALUES('29067','APP 1','12/19/2017 00:03:00')
INSERT INTO @LOG VALUES('32337','APP 1','12/19/2017 00:09:00')
INSERT INTO @LOG VALUES('23265','APP 1','12/19/2017 00:16:00')
INSERT INTO @LOG VALUES('09343','APP 1','12/19/2017 00:20:00')
INSERT INTO @LOG VALUES('20901','APP 1','12/19/2017 00:35:00')
INSERT INTO @LOG VALUES('26786','APP 1','12/19/2017 00:47:00')
INSERT INTO @LOG VALUES('24980','APP 1','12/19/2017 00:49:00')
INSERT INTO @LOG VALUES('25996','APP 1','12/19/2017 00:52:00')
INSERT INTO @LOG VALUES('14949','APP 1','12/19/2017 01:03:00')
INSERT INTO @LOG VALUES('54987','APP 1','12/19/2017 01:47:00')
INSERT INTO @LOG VALUES('23265','APP 1','12/19/2017 04:53:00')
INSERT INTO @LOG VALUES('97697','APP 2','12/18/2017 18:15:00')
INSERT INTO @LOG VALUES('97697','APP 2','12/18/2017 18:18:00')
INSERT INTO @LOG VALUES('97697','APP 2','12/18/2017 18:25:00')
INSERT INTO @LOG VALUES('15841','APP 2','12/18/2017 19:10:00')
INSERT INTO @LOG VALUES('20635','APP 3','12/19/2017 06:14:00')
INSERT INTO @LOG VALUES('25081','APP 3','12/19/2017 06:38:00')
INSERT INTO @LOG VALUES('61660','APP 3','12/19/2017 06:42:00')

SELECT DISTINCT [PageView],(SELECT COUNT(*)/(SELECT COUNT(DISTINCT CONVERT(VARCHAR(10), DateHour, 111)) FROM @LOG) FROM @LOG l2 WHERE l1.PageView=l2.PageView) [Count] 
FROM @LOG l1
GROUP BY PageView,CONVERT(VARCHAR(10), DateHour, 111)

Output

PageView Count

APP 1 12

APP 2 2

APP 3 1

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Calculate Average of each group in SQL Server

Company

Explore

Follow us

Contact Us